Performing insert/update/delete together for a target table
Hi,
I have a job wherein I'm transferring data from sql server to mysql. Job runs every 10 minutes. I want the rows to be deleted/inserted/updated in mysql as per the changes in sql server.
Do I have to make 2 separate outputs from tmap to tmsqloutput? One for 'Insert/Update' and the other one for 'Delete'? If yes, then would there not be lock in the table due to simultaneous insert/update/delete?
Any idea on this would be helpful.
Thanks in advance
Rathi
Hi,
With community version, there is a only way to achieve your goal. Matching the table and comparing all records to conclude the needed inserted records, updated records, and deleted records.
If you want to capture the changed data and only load these changed data into target table to achieve table sync, you can compare tables by using tMap.
The work flow should be: Target DB-->tMap(make inner join on your input and set the "Catch lookup inner join reject" as true)-->output
Source DB-->
The output will be the changed data.
With Talend Enterprise Subscription Version, there is a CDC (Change Data Capture) feature which can achieve data sync, however, it only take action in one DB.
Hi Sabrina,
Thanks for the response. I am still unable to understand how do I do a insert/update/delete in one shot? Because there is 'Insert/update' and 'Delete'. How do I put these together?
I understand Enterprise, gives CDC. But I am supposed to do this in TOS itself
Thanks
Rathi
Hi Sabrina, Thanks for the response. I am still unable to understand how do I do a insert/update/delete in one shot? Because there is 'Insert/update' and 'Delete'. How do I put these together? I understand Enterprise, gives CDC. But I am supposed to do this in TOS itself Thanks Rathi
You can not do this in one step, or parallel as mentioned in original question
You must do this as sequence as combination main and lookup flows + tMap JOIN
or You can load data to temporary table and manage all by SQL code
Yes I understand it must be done in sequence. So, the flow,
tmssqlinput --> tmap --> tmysqloutput(insertupdate)
|--> tmysqloutput (delete)
My question is tmap is outputting, both insert/update and delete at the same time. How will the table handle this simultaneously?
Thanks
Rathi
Yes I understand it must be done in sequence. So, the flow, tmssqlinput --> tmap --> tmysqloutput(insertupdate) |--> tmysqloutput (delete) My question is tmap is outputting, both insert/update and delete at the same time. How will the table handle this simultaneously? Thanks Rathi
do not know - You can test
I use always other ways:
collect changes (when it possible), and then apply to target - it is best method. In my case it possible when we have 2 timestamps on database and soft delete (like 'deleted' == true), by timestamp very easy to understand - new records or edited from last iteration
use table on target database as mean above and call stored procedure to do this, benefits - use indexes and fast.
make a 3 separate step (not from 1 tMap, but 3 separate tMap and 3 query), if data not huge:
check records for UPDATE - and update all what possible
check new records - INSERT them
check what need delete - and delete them
why 3?
because in this case You can use batch mode for UPDATE/INSERT/DELETE and total speed much faster even after 3 query over databases
Ok. Maybe I can use 'Update/Insert' and then 'Delete'. How do I design the job to run 'Delete' after 'Update/Insert'?
I know sql procedures can accomplish this.
But, I want to know how to do it in Talend. To call Delete after insert/update?
Thanks
