I have a scenario like below,
Daily i get a source file with many records which I will be updating into a table.
Sample source file is like below,
Whenever I have two records for a particular EMPID, first I should update the date column of already existing record for that EMPID and then insert the first record with salary change,
then next step again update the second record inserted and insert the third record.
Find below the before and after status of table,
Table before any insert or update :
Table after the update1, insert1, update2, insert2 :
The update/insert key is EMPID.
So it's not really and update.... it's more like an insert but your PK is EMPID + UPDATE_DATE. You can do it like that : just add in your update/insert key the UPDATE_DATE column
Or else, you can take information about SCD (Slowing Changing Dimension), and use an SCD component
Good luck ,