How to archive SCD type 2 functionality using Merge in SQL server

_{Friday, July 26, 2019 9:28 AM}

Hi All,

I have one scenario like receiving daily file data from source now i want to created SCD type 2 functionality to identify new records and changes in source data with active flag.

Cases:

1) New records will inserted into target table

2) Source matches existing records in target and data changes in source.

 updated records active flag old record and insert new record into target table.

3) Missing records from source:

Update active flag as old.

I have used merge statement and achieved only 1st and 3rd cases.

How to achieved 2nd case. 

Please share your idea.

Regards,

Vaishu

_{Friday, July 26, 2019 9:41 AM}

Please check if below blog helps you:

https://www.mssqltips.com/sqlservertip/2883/using-the-sql-server-merge-statement-to-process-type-2-slowly-changing-dimensions/

Cheers
Vaibhav
MCSA (SQL Server 2012)