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
Uma
It 's an amazing and awesome blog. Thanks for sharing
ReplyDeleteMsbi Online Training in India
Msbi Training