Tuesday, 2 September 2014

Implement Insert (for new) and update (for existing) strategy in talend (tmap update/insert/reject)


Implement Insert (for new) and update (for existing) strategy in talend

Most of times in dwh/etl/data integration loads we have incoming source data feeds and we have to merge (update/insert) this with existing data in target table.
There are multiple ways to do this, i will explain one of the method i have used.

You have a source table - say srcemployee
you want to merge this data after doing lookup with target - tgtemployee
keys for lookup is say empid

all we need is source input, target input(lookup) tmap and toutput
in tmap we would join source input with target input on key column and select join options as all matches inner join. In tmap output we will have two groups once for handling matching records and other for handling rejects (records which were not satisfying the join condition) these are insert.

overall job (tmysqloutput_1 insert, tmysqloutput_2 is for update)



schema of input srcemployee

schema of input srcemployee


tmap join condition
tmap join condition

mention key for update in schema for tmysqloutput_2
mention key for update in schema for tmysqloutput_2