Obtain the number of records, then divide by a fixed number (say 20,000) add one and I got the number of times I have to do a dynamic SQL to read the total data.
So I get this number (33). It is the number of calculated times I have to do my sequence of read from the source and then insert on the target.
Now I face with the following scenario:
Got number (single number in a variable)
Loop (33 times)
Select data (chunk of 20,000 records)
Loop
insert data (20,000 rows)
The number of records varies day a day, sometimes is less than 40,000 other 500K+ records. So fixed document is not an option.
I am pretty new with IS, but I have worked with several ETL tools.
I am going to try the following:
Develop a query that populate a document, from there a just loop thru the document and query the data.
Pseudocode
Query to get count
Divide result by (20,000)
Result + 1 to get the number chunks of 20,000 (or less) records
generate document
result[0]
min = 1
max = 20000
result[1]
min = 20001
max = 40000
…
Loop document
use dynamic SQL to get
Loop result of Dynamic SQL
insert records
Now, I have never used nested loops, and I am a little confused. The first loop generates a $iteration variable, but so the second loop. Does IS knows and keeps two counters with the same name, separately?