In this post , i will share a IKM Knowledge Module that can make Delete/Insert operation which sometimes a better alternative for Incremental Update.
What i want to do is Inserting resultset of the following query into my target table FINAL_SET2 and while doing that i want to delete the existing rows.
select FINAL_SET.CUST_ID CUST_ID,
from ODI_DEMO.FINAL_SET FINAL_SET , ODI_DEMO.FINAL_SET2 TRG
where (1=1) and (FINAL_SET.CUST_ID = TRG.CUST_ID)
At First i am changing my target tables alias name to TRG ( need for Knowledge Module )
My Source table :
My Target Table: (FINAL_SET2)
What i expect is executing my mapping with my new IKM and replace the three rows showed above with the ones in the source table.
Preparing mapping object according to our query.
Setting my Knowledge Module
The KM basically has 3 steps. First i am creating a temp table which is as same as my target table.By this way before deleting the rows in my target , i can save the resulset into another table for upcoming insert operation.
We just inserted the resulset into temp table and now we can delete the existing records.
And for final step, New resultset data which is inside of our temp table will be inserted into our target table.
Final view of our target table:
You can download the KM file below: