[resolved] Capture Insert Update Delete records on all source tables

One Star

[resolved] Capture Insert Update Delete records on all source tables

Hi All,
I have around 50+ tables in the source Database for which i would like to identify the Insert, Delete and Updated records and load only those records in Destination database.
I have taken the following approach to identify Insert/Update/Delete records on source and load the same to Destination table:
Step 1: Identify Newly Inserted records: Source Database table is mapped as main row to tMap and Destination Database table is mapped as look up row to tMap. Both input tables are inner joined on Primary Key. Output table in tMap has Catch Lookup inner join reject property set to true and output of tMap is mapped to Destination database with action on data as Insert.
Step 2: Identify Updated records: Source Database table is mapped as main row to tMap and a table which maintains the history of job runs is look up table to tMap. Output table of tMap is mapped to Destination database with action on data as update. Output of tMap has a condition - TalendDate.compareDate(row1.LAST_UPD,row4.LastModifiedDate,"yyyy-MM-dd") == 1 && row4.LastModifiedDate != null
Step 3: Identify Deleted records on source: This is similar to Step 1, but Main row and look up row are exchanged and action on data on output of tMap is delete.
Can some one please suggest if i Should create one job per source table with above 3 steps or is there a way to handle all 50+ jobs in a single job? Currently i am creating one job per one table which is taking lot of time in setting up the job.
Best Regards,
Kalyan
Moderator

Re: [resolved] Capture Insert Update Delete records on all source tables

Hi,
Could you please take a look at related forums to see if it is OK with you?
http://www.talendforge.org/forum/viewtopic.php?id=38871
http://www.talendforge.org/forum/viewtopic.php?id=38989
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: [resolved] Capture Insert Update Delete records on all source tables

Hi
The 2 topics are specifically talking with regards to one table loading. But in my case, i am talking about 50+ tables. so should i have one such job for each table?
Best Regards,
Kalyan
Moderator

Re: [resolved] Capture Insert Update Delete records on all source tables

Hi,
With community version, you have to compare table one by one.
For Talend Enterprise Subscription, here is a CDC(change data capture) feature which can capture only the changed source data and to move it from a source to a target system(s) in real time.
Please take a look at document https://help.talend.com/search/all?query=CDC+architectural+overview&content-lang=en

Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: [resolved] Capture Insert Update Delete records on all source tables

I have gone through the CDC approach but since it creates triggers at source database, we are not in favor of taking that approach. So i understand that i should create n number of jobs to achieve CDC in my way for n source tables.
Moderator

Re: [resolved] Capture Insert Update Delete records on all source tables

Hi kalyangupta,
I have gone through the CDC approach but since it creates triggers at source database, we are not in favor of taking that approach. So i understand that i should create n number of jobs to achieve CDC in my way for n source tables.

Thanks for your feedback. Don't hesitate to let us know if there is any further help we can give.
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Seventeen Stars

Re: [resolved] Capture Insert Update Delete records on all source tables

hi,
if you're using Oracle you can use non intrusive redo log with CDC.
regards
laurent
One Star

Re: [resolved] Capture Insert Update Delete records on all source tables

Source is Oracle but destination is SQL Server. Can i still take an approach to of non intrusive redo log with CDC?
Seventeen Stars

Re: [resolved] Capture Insert Update Delete records on all source tables

as far as I know, there's no reason you cannot do it 
https://help.talend.com/pages/viewpage.action?pageId=21248341
regards