Five Stars

Sequential update and insert

 

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!

  • Data Integration
1 REPLY
Six Stars sgv
Six Stars

Re: Sequential update and insert

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 Smiley Wink

 

Good luck ,

SGV