Hi ,
I'm looking for inputs to identify and delete records from table 1.
I have 2 tables .Table 1 and Table 2. I have found a way to identify the inserts and updates from table 1 using the output tool (update ,insert if new option) and with left outer and union could accomplish this.
However , I'm trying to find a way to do deletion of records into table 2, ( I have to identify the records that are removed from Table 1 on daily basis and perform those deletes into table 2 ). I have a strategy to use select all from table 2 and left outer join with table 1 where the primary key is null , to identify the deletes, But not sure which transformation or tool i should use post this to get these deletes . Looked at output tool and there is no such option to pass filter in the sql.
Can some one pls suggest any work around here . Need to implement this by next week and out of options
I have added the current working workflow here in order to better understand my need and help update or add the delete flow into this under similar lines.
Appreciate any inputs !
Solved! Go to Solution.
It is great!!!
how did you remove the record one to one?
Thx.
Hi - I created a temporary table in my putput tool which will track all the deleted records and then added a post sql delete to remove those from that table. To identify the deleted records i used outer join and is_null condition to find the records that are removed. which worked good.
However now im facing a new challenge and added a new post for that. which is addressing forign keys . Deletion now happens if there are no dependencies on the records . But if the records primary key is referenced in other tables update/delete is failing with foreign key violation. So i need to take care of those tables first .
I know the tables, but not sure how to add these dependencies in my current workflow .