Sequential update and insert

[rajesh90]

Team,

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,

EMPID,NAME,SALARY
100,Raj,1000
100,Raj,2000
200,Ram,3000
200,Ram,4000
300,Peter,5000
400,Anto,6000

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 :

EMP ID	NAME	SALARY	UPDATE DATE
100	Raj	500	31-Dec-99

Table after the update1, insert1, update2, insert2 :

EMP ID	NAME	SALARY	UPDATE DATE
100	Raj	500	26-Aug-17
100	Raj	1000	27-Aug-17
100	Raj	2000	31-Dec-99

The update/insert key is EMPID.

Please help!

[Anonymous]

Hi ,

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 ,

SGV