Insert update and delete on Target

Get new comments by email
You can cancel email alerts at anytime.
Comments (2)

sree in Flemington, Australia

95 months ago

I have a source and target and I need to update insert and delete the target based on the source.
For this I made a mapping with Dynamic lookup and two update strategy one insert and the other for update.
This is working fine with inserts and updates, now I need to delete the records in target which are not in source.

Note*** I cannot do truncate or delete on all the records in the target it got some child tables I have to delete if the records is not in source.
This is going to be a Cascade Delete, so we can delete records which will delete all the records in child tables.

This is my current mapping which is working for insert and update..

Source ------> Source Qualifier ----->Dynamic Lookup ------>Router ------->update(insert) -------> Target
------->update(update) -------> Target

So now I need the delete logic how can I implement ……????????????

- Was this comment helpful? Yes / No (2) Reply - Report abuse

AA in Alpharetta, Georgia

64 months ago

Anu in Toronto, Ontario said: sree,

You may have to develop a seperate pipeline in your mapping.Once ur insert and delete operation is over,
in the second pipe line use a SQ for the Target and your source using the Business key create an SQL with MINUS operator which will give you the keys not present in the source .With those keys you can identify the target keys by a lkp and then the child keys .using an update stratergy delete the corresponding rows in the child tables and in the parent.

can you be specific about how SQL MINUS operation is being performed ?

thnx,
AA - Lansing, MI.

- Was this comment helpful? Yes (1) / No (4) Reply - Report abuse

» Sign in or create an account to comment on this topic.