In this post , i will share a IKM Knowledge Module that can make Delete/Insert operation which sometimes a better alternative for Incremental Update.
Case Study:
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.
Sample Query:
select CUST_ID,
CUST_NAME,
PRODUCT,
TOTAL_NUM_OF_ORDERS,
PER_QUANTITY
FROM (
select FINAL_SET.CUST_ID CUST_ID,
FINAL_SET.CUST_NAME CUST_NAME,
FINAL_SET.PRODUCT PRODUCT,
FINAL_SET.TOTAL_NUM_OF_ORDERS TOTAL_NUM_OF_ORDERS,
FINAL_SET.PER_QUANTITY PER_QUANTITY
from ODI_DEMO.FINAL_SET FINAL_SET , ODI_DEMO.FINAL_SET2 TRG
where (1=1) and (FINAL_SET.CUST_ID = TRG.CUST_ID)
)
Step1:
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.
Step2:
Preparing mapping object according to our query.
Step3:
Setting my Knowledge Module
Step4:
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.
Step5:
We just inserted the resulset into temp table and now we can delete the existing records.
Step6:
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:
Note:
You can download the KM file below:
http://speedy.sh/dwcx4/KM-IKM-SQL-Control-DeleteInsert-2.xml
Hiç yorum yok:
Yorum Gönder