SQL Merge + SCD ( slowly changing dimensions)

Sql server Merge is a powerful t-sql statement which can be used to write simple code to do insert, update, delete in one statement instead of writing 3 different statements one for update, one for insert & one for delete.  Using Merge is cleaner & much more efficient when it executes.

Syntax looks like this :


merge in to  dbo.destination as d
using dbo.source as s
on (s.pk= d.pk)
when matched
            then update set d.col1= s.col1
when not matched
         then insert....

How ever Merge can do much more than what is seen here, There is an excellent presentation by Kimball group about how Merge can be used in SCD scenario :

http://www.kimballgroup.com/html/08dt/KU107_UsingSQL_MERGESlowlyChangingDimension.pdf