Using Merge statement for SCD type 2

When we are in Type2

1. Insert new rows in case of new records
2. Update old rows in case of mismatch for value columns like State of residence for person , make end date as null
3. Insert new records in case of changes and make end date as 31-12-9999

If we use Merge statement in oracle we get only 2 options . Insert or Update . If a new record easy to insert . If a existing record we have 2 steps

1. End date the current record

2. Insert the new record

Since merge executes only once per record there is no way of doing insert and update for same record . So we have to create a dataset which has 2 records in case of SCD Type2 changes. Which means we will have the same record twice , with row_type_indactor . When 1 then insert and when 2 then update .

Key	Value
1	A
2	B

Key_code	State
2	Hyd

When we join on Condition Key = Key_code   then we will get only one row

Key_code	State
2	Hyd

When we join on Condition Key < =  Key_code   then we will get only one row. This is because 2 is greater than 1 and 2 is equal to 2. So 2 rows. It looks simple but took me more than 1 hour to figure out

Key_code	State
2	Hyd
2	Hyd

Below is a good link on the subject

http://databobjr.blogspot.in/2011/04/load-slowly-changing-dimension-type-2.html