One Star

Insert every 100 row in table

Hi,
I have a problem statement.
I have X number of rows from input and I have to pass 1st row,100th row, 200th row and so on till last record.
Last record should also be insert in target table.
Thanks,
Saurabh
4 REPLIES
Six Stars JR
Six Stars

Re: Insert every 100 row in table

Hi,
Please answer the following questions first as a possible solution depends on the components to be used:

What is the source of your data? A delimited file? A database table? Is it the output of an already existing part of your job?
What is the target? Is it an Oracle or MySQL table? Is it something different?

Regards, Joachim
Ten Stars

Re: Insert every 100 row in table

Append a row number with simple sequencer to your input data using a tMap formula of:
Numeric.sequence("s1",0,1)
Your input component should set a variable NB_LINE that you can reference to get the row count.  The exact variable name varies depending on the name of your input component:
((Integer)globalMap.get("tPostgresqlInput_1_NB_LINE"))
I'm not sure if you can reference the output of that sequence in the same tMap, but if not, add a second tMap with a formula:
(rownum % 100 == 0 || rownum == (Integer)globalMap.get("tPostgresqlInput_1_NB_LINE")) ? "KEEP" : "REJECT"
A tFilter will reduce the set down to the records to keep, then a database output component can store them.
Six Stars JR
Six Stars

Re: Insert every 100 row in table

If you follow the solution suggested above, you will have to add a condition for the first record as well. Put this in the tMap expression filter (top right of the output - arrow with the "+" sign on top):
rownum % 100 == 0 || rownum == 1 || rownum == ((Integer)globalMap.get("tPostgresqlInput_1_NB_LINE"))
But again, the exact setup depends on your input and output components.
Ten Stars

Re: Insert every 100 row in table

Yes, I tried to get cheeky and fix the first row problem by starting the sequencer at 0.  In Java, 0 mod anything returns 0.  However, the rest of the rows would then be off by one, so you'd effectively be inserting rows 99, 199, 299, etc.