topic Re: [resolved] Capture Insert Update Delete records on all source tables in Talend Studio

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

satyavar — Tue, 28 Oct 2014 05:29:16 GMT

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

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

Anonymous — Tue, 28 Oct 2014 07:12:57 GMT

Hi,
Could you please take a look at related forums to see if it is OK with you?
https://community.talend.com/t5/Design-and-Development/resolved-Search-for-new-or-modified-Artikels-to-update-in-other/td-p/62315
https://community.talend.com/t5/Design-and-Development/resolved-Compare-two-tables-and-delete-few-records-in/td-p/63293
Best regards
Sabrina

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

satyavar — Tue, 28 Oct 2014 07:21:09 GMT

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

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

Anonymous — Tue, 28 Oct 2014 07:32:19 GMT

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

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

satyavar — Tue, 28 Oct 2014 07:55:17 GMT

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.

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

Anonymous — Tue, 28 Oct 2014 08:01:10 GMT

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

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

Anonymous — Tue, 28 Oct 2014 11:00:52 GMT

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

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

satyavar — Tue, 28 Oct 2014 11:02:39 GMT

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

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

Anonymous — Tue, 28 Oct 2014 11:12:04 GMT

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