Insert every 100 row in table

One Star

Insert every 100 row in table

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.
Nine Stars JR
Nine Stars

Re: Insert every 100 row in table

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:
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:
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.
Nine Stars JR
Nine 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.

Calling Talend Open Studio Users

The first 100 community members completing the Open Studio survey win a $10 gift voucher.

Start the survey


Talend named a Leader.

Get your copy


Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables


How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration


Downloads and Trials

Test drive Talend's enterprise products.