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