SQL 2008 Merge Operation

MERGE is a new command introduced in SQL 2008. This is generally used to compare two tables and insert, update and delete rows in a single statement instead of writing separate statement for each process.

MERGE SYNTAX
MERGE FactSales AS TARGET USING Stg_Sales AS SOURCE ON (FactSales.OrderID = Stg_Sales.OrderID)
WHEN MATCHED AND TARGET.SalesAmount <> SOURCE.SalesAmount OR TARGET.ProductID <> SOURCE.ProductID
THEN UPDATE SET TARGET.ProductID = SOURCE.ProductID, Target.SalesAmount = SOURCE.SalesAmount
WHEN NOT MATCHED BY TARGET
THEN
INSERT (ProductID, SalesNo, SalesAmount, SalesOn)
VALUES (SOURCE.ProductID, SOURCE.SalesNo, SOURCE.SalesAmount, SOURCE.SalesOn)
WHEN NOT MATCHED BY SOURCE
DELETE

This Merge statement can be well used in SSIS SCD Type 1 and 2 also in Incremental load.