topic Re: Insert every 100 row in table in Talend Studio

Insert every 100 row in table

_AnonymousUser — Thu, 10 Nov 2016 17:57:06 GMT

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

Re: Insert every 100 row in table

JR1 — Thu, 10 Nov 2016 20:52:03 GMT

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

Re: Insert every 100 row in table

cterenzi — Fri, 11 Nov 2016 20:52:09 GMT

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.

Re: Insert every 100 row in table

JR1 — Sun, 13 Nov 2016 19:41:10 GMT

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.

Re: Insert every 100 row in table

cterenzi — Mon, 14 Nov 2016 21:51:54 GMT

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.