topic Re: Tmap usage during insert/update in Talend Studio

Tmap usage during insert/update

Anonymous — Tue, 31 Jan 2017 11:05:26 GMT

Hi,

I have a job wherein the source is sql server and destination is mysql. Table with same columns and data types exist in both the databases. The job has to insert rows that are not present in mysql and update rows that are already present in mysql.

The current implementation is tmssqlinput -- tmap -- tmysqloutput. I have set "Insert or Update" in the tmysqloutput component. Will this work for my requirement?

What I do not understand is, when must I use a tmap and use mysql as lookup and sqlserver as main input? When is the scenario to use "Catch lookup inner join reject"?

Any ideas on this would be of great help

Thanks in advance
Rathi

Re: Tmap usage during insert/update

Anonymous — Tue, 31 Jan 2017 11:08:15 GMT

If you want extract data from the sql serve and insert into mysql server you don't need the tmap component.

use tmssqlinput --> tmysqloutput

remember to use insert or update.

Re: Tmap usage during insert/update

Anonymous — Tue, 31 Jan 2017 11:23:57 GMT

Thanks silverhand.
Will you also please explain when must I use lookup to update a destination table? When is the scenario to use "[color=#5b5b5d][font=Verdana, Helvetica, Arial, sans-serif] Catch lookup inner join reject[/font][/color]"? I am unable to understand its usage.
Thanks in advance
Rathi

Re: Tmap usage during insert/update

Anonymous — Tue, 31 Jan 2017 11:30:28 GMT

Hi Rathi.
The lookup is used when you want to "merge" to different tables linked togheter with external key.
The Catch lookup inner join reject will able you to catch all data that are in the first column but not in second one (if you linked correctly the the table).

Re: Tmap usage during insert/update

Anonymous — Tue, 31 Jan 2017 12:13:11 GMT

That explains. Thanks silverhand.

Re: Tmap usage during insert/update

vapukov — Tue, 31 Jan 2017 20:45:43 GMT

silverhand wrote:
Hi Rathi.
The lookup is used when you want to "merge" to different tables linked togheter with external key.
The Catch lookup inner join reject will able you to catch all data that are in the first column but not in second one (if you linked correctly the the table).

Just for add:
Sometime (You always must test different ways), sometime You can - load data from both servers in Talend, and with tMap make lookup and split records for NEW and UPDATE flows, then use 2 tMySQLOutput components - one make batch INSERT, 2nd batch UPDATE
The benefits it is speed
- tMap in memory lookup - is fast if You have adequate resources (You may need adjust JVM parameters for BIG tables)
- 2 separate flow with INSERT and UPDATE could work 100+ times faster than INSERT with UPDATE on duplicate keys