Working with Merge in SQL Server 2008

This is a new feature of SQL Server 2008 T-SQL language that lets you do INSERTs, UPDATEs, and DELETEs in a single SQL statement. A very common requirement in the ETL process while loading data is to compare the source and the target table(using a key) to determine what action needs to be taken:
i. If data doesn't exist in the target table, then insert data into the target table.
ii. If data in the target table exists, then update the target table.
iii. If the data exist in the target table but not in the source table, then delete the data from the target table.
The Merge command in SQL Server 2008 will let you do all the above actions in a single statement. Lets see how it works using a very simple example. Lets say we have a table called SalesOrder and another table called SalesOrderReporting. To make it simple for this post, the two tables are similar in structure and currently they are in sync with only 3 records as shown in the figure below.

I will do an insert, an update and a delete operation on SalesOrder table and use merge to update the same operations on the SalesOrderReporting table. As shown in the figure below, the SalesOrder table has been modified with an insert, an update and a delete operation. So the two tables are out of sync now.

We will use Merge statement to update the same operations on SalesOrderReporting table by comparing it with SalesOrder table using the SalesOrderID key. Here is the code I used.

MERGE SalesOrdersReporting
USING SalesOrders ON SalesOrders.SalesOrdersID = SalesOrdersReporting.SalesOrdersID
WHEN Matched
THEN UPDATE SET SalesOrdersReporting.OrderQty = SalesOrders.OrderQty,
SalesOrdersReporting.UnitPrice = SalesOrders.UnitPrice,
SalesOrdersReporting.UnitPriceDiscount = SalesOrders.UnitPriceDiscount
WHEN NOT MATCHED BY TARGET
THEN INSERT (OrderQty, UnitPrice, UnitPriceDiscount)
VALUES(OrderQty, UnitPrice, UnitPriceDiscount)
WHEN NOT MATCHED BY SOURCE
THEN DELETE;

After executing the above code, the two SalesOrdersReporting will be updated and the two tables will be back in sync as shown in the figure below.

To help you understand the code, here is the (simplified) syntax of the Merge statment:

MERGE TargetTable
USING SourceTable
ON merge condition(joins etc)
WHEN Matched
THEN UPDATE....
WHEN NOT MATCHED BY TARGET
THEN INSERT...
WHEN NOT MATCHED BY SOURCE
THEN DELETE;