The Business Intelligence Blog

February 9, 2010

Working with Merge in SQL Server 2008

Filed under: SQL Server Database — Tags: , , , , — Shahfaisal @ 5:47 am

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;

Advertisements

1 Comment »

  1. […] 14. What is “Merge” command in SQL Server 2008? How does it work? Merge is a new feature of SQL Server 2008 T-SQL language that lets you do INSERTs, UPDATEs, and DELETEs in a single SQL statement. For more details, see this post – Working with Merge in SQL Server 2008 […]

    Pingback by SQL Server Database Administration – FAQs « The Business Intelligence Blog — February 9, 2010 @ 8:24 pm


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: