So, I have to do some ETL stuff…

I have to move 600,000+ records on daily basis.

I am doing this so far.

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.

Thank you in advance for your advice.

Regards

you can think of writing your code in Java also.
using java you can open connection, execute sql and perform operation on data.

Thank you.

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?

Thanks!!!

I don’t see a need for nested loop. but if you want to go ahead with nested loop then don’t worry about $iteration variable. IS will take care

One loop is for select chunks of records
inside that loop I need another loop to insert records on destination database.

Thank you for your tips.

Regards.

you can think of using BatchInsert to avoid looping.
Or get data base people to write a stored procedure for you to execute from webMethods.

Totally agree with you, this is a “thinking out of the box” service.

It is working now, slower than other available methods, but help us to test how to break data and process it.

Thank you very much.

Regards.