Monday, January 2, 2017

How to use merge instead of slowly changing dimension

Performs insert, update, or delete operations on a target table based on the results of a join with a source table.
MERGE INTO dbo.productdata AS target
USING dbo.staging_productdata AS source
   ON target.ProductNumber = source.ProductNumber
   AND target.DateTime = source.DateTime
WHEN MATCHED THEN
   UPDATE SET target.product = source.product
WHEN NOT MATCHED BY TARGET THEN
   INSERT (ProductNumber, DateTime, product)
   VALUES (source.ProductNumber, source.DateTime, source.product)


--With delete
MERGE INTO dbo.productdata AS target
USING dbo.staging_productdata AS source
   ON target.ProductNumber = source.ProductNumber
   AND target.DateTime = source.DateTime
WHEN MATCHED THEN
   UPDATE SET target.product = source.product
WHEN NOT MATCHED BY TARGET THEN
   INSERT (ProductNumber, DateTime, product)
   VALUES (source.ProductNumber, source.DateTime, source.product)
WHEN NOT MATCHED BY SOURCE THEN
   DELETE

Cheers!
Uma

1 comment: