I'm using Informatica Power Center 9.1.0 and have blocked with below usecase.
For any given key, delete existing records from target table and insert the records from soruce to same target table and both delete and insert should happen in a single transaction.
Source data:
BusDate Order Product Quantity
21-May O1 Phone 100
21-May O1 Tab 50
21-May O1 Cam 75
21-May O1 PC 200
21-May O2 Phone 150
21-May O2 PC 50
Source data is as mentioned in above tabular fomrat, when we are writing to target, delete existing data for given business date and order id combination and insert new data in same transaction.
In above example for Order 1 (O1) and BusDate 5/21 we want to delete all existing records in target table and insert all 4 records in same transaction.
Here how it looks if I need to do same in SQL:
BEGIN TRANSACTION
DELETE target_table
WHERE BusDate = '5/21/2014'
AND Order = 'O1'
INSERT target_table
SELECT *
FROM input_table
WHERE BusDate = '5/21/2014'
AND Order = 'O1'
COMMIT TRANSACTION
