topic Re: Insert records into multiple target tables based on Multiple Row counts in Talend Studio

Insert records into multiple target tables based on Multiple Row counts

Viswa560 — Mon, 04 Sep 2017 14:33:26 GMT

Let’s say I have more than have record in source table and I have 3 destination table A,B,C.

I have to insert first 1 to 10 records in A then 11 to 20 in B and 21 to 30 in C.
Then again from 31 to 40 in A, 41 to 50 in B and 51 to 60 in C… So on up to last record.

How to Achieve this?

Thanks In Advance.

Re: Insert records into multiple target tables based on Multiple Row counts

TRF — Mon, 04 Sep 2017 14:47:29 GMT

3 output in a tMap with the right expression filter for each should be a solution.

The expession may be based on a sequence evaluation (see Numeric.sequence).

There many possibilities to implemente this king of algorithm, for example, have a 1st sequence varying from 1 to 10, each time it arrives to 10, restart to 1 and increment a 2nd sequence (varying from 1 to 3) which is used to decide which output the current record must go to.

The algorithm can be built using a tJavaRow (so you have tFileInputDelimited -> tJavaRow -> tMap -> tFileOutputDelimited 1, 2 or 3.

Re: Insert records into multiple target tables based on Multiple Row counts

TRF — Mon, 04 Sep 2017 18:59:38 GMT

To complete my 1st response, here is how it should look like:

In this example, data comes from tFixedFlowInput and consists of 12 records.

The exercise is to dispatch rows on 3 output (like you) for every group of 3 lines (instead of 10).

Here is the tJavaRow code:

if ((Integer)globalMap.get("s2") == null || ((Integer)globalMap.get("s2")) > 2) {
	Numeric.resetSequence("s2", 0);
	if ((Integer)globalMap.get("s1") == null || ((Integer)globalMap.get("s1")) > 2) {
		Numeric.resetSequence("s1", 0);
	}
	globalMap.put("s1", Numeric.sequence("s1", 1, 1));
}
globalMap.put("s2", Numeric.sequence("s2", 1, 1));

output_row.id = input_row.id;
output_row.amount = input_row.amount;
output_row.s1 = (Integer)globalMap.get("s1");
output_row.s2 = (Integer)globalMap.get("s2");

Fileds s1 and s2 are here only for the demonstration and can be removed.

Here is the tMap with the expression filter for each output:

And the result with 3 distinct groups as expected:

Hope this helps (don't forget to mark the topic as solved if so - Kudo also accepted).

Re: Insert records into multiple target tables based on Multiple Row counts

TRF — Wed, 06 Sep 2017 22:04:57 GMT

Let me know if it helped you, and mark the case as solved if the answer satisfied you (Kudo also accepted).