Generating range of numbers 1...n in SAP HANA

In SAP HANA, you can generate a range of numbers from 1 to n using different approaches. This is particularly useful when you need to populate tables with sequential data or create test datasets.

Method 1: Using FOR Loop

You can use a FOR loop to iterate through a range of numbers and insert them into a table ?

FOR START_CID IN 1..1000 DO
    INSERT INTO "TEST_TABLE" VALUES(START_CID, '');
END FOR;

This loop will iterate from 1 to 1000 and insert each number as a CID value along with an empty string into the TEST_TABLE.

Method 2: Using Series Generator

You can also use the SERIES_GENERATE_INTEGER function as a more efficient generator approach ?

INSERT INTO "TEST_TABLE" 
SELECT GENERATED_PERIOD_START as CID, '' as CNAME 
FROM SERIES_GENERATE_INTEGER(1,1,1001);

The SERIES_GENERATE_INTEGER function takes three parameters: start value (1), increment step (1), and end value (1001). Note that the end value is exclusive, so using 1001 will generate numbers from 1 to 1000.

Key Differences

The FOR loop method executes individual INSERT statements for each iteration, while the series generator method performs a single bulk INSERT operation, making it more efficient for large datasets.

Conclusion

Both methods effectively generate sequential numbers in SAP HANA, with the series generator being more efficient for bulk operations and the FOR loop providing more control for complex logic during iteration.