SQL Server 2008 – Merge Capability

MERGE is a new feature that provides an efficient way to perform multiple DML operations.

In previous versions of SQL Server, we had to write separate statements to INSERT, UPDATE, or DELETE data based on certain conditions, but now, using MERGE statements, we can include the logic of such data modifications in one statement that even checks when the data is matched. If it matches, update it.  If it's new, insert it.

One of the most important advantages of MERGE statement is that all the data is read and processed only once. This is quite an improvement in performance of database query.

Remember to Install AdventureWorks2008

See my blog about installing adventureworks2008. It wasn't trivial to me.

You should see AdventureWorks2008


Perform the following
SELECT     ProductID, LocationID, Shelf, Bin, Quantity, ModifiedDate

FROM        Production.ProductInventory

WHERE      (Quantity = 0)

1. Start SQL Server 2008 Management Studio

2. Select "New Query" from the toolbar


3. Use AdventureWorks2008


4. Type in or paste in the code from above

5. Select "Query, Execute" from the menu


Create the replenishment table



Steps: Paste them both in

Notice both selects


This table will be used to update our inventory. We can pretend that this table will indicate which products we got back from our supplier.

It will be the source table.

The ProductInventory table will be the target table.

Data in the Replenishment table


Remember, the target table is ProductInventory.

Is this what you might do by hand?

853-add inventory
859-add inventory
882-leave alone
What would you want to happen to the ProductInventory table, given the Replenishment table.

For product 853, we are getting 250 more.

For product 859, we are getting 500 more.

For product 876, nothing came back.

This query lists the key ProductIDs used in this replenishment example.
select * from Production.ProductInventory
where ProductID in (853,859,876,882)



The replenishment table.


You can copy this merge statement below:
MERGE [Production].[ProductInventory] tgt
  USING [dbo].[Replenishment] src
   ON (tgt.[ProductID] = src.[ProductID])
  WHEN MATCHED AND (src.[OrderQty] > 0) THEN
    UPDATE SET tgt.[Quantity] += src.[OrderQty]
Running the merge statement.


Notice the following:

The appropriate updates happened for 853 and 859.

Notice 882 is still there because replenish never asked for more.

Notice that 876 is gone because we asked for replenishment but got 0 back; therefore it was deleted.


The snippet tool provides many of the commands used with the AdventureWorks2008 database.


Comments (2)

  1. Thank you for submitting this cool story – Trackback from DotNetShoutout

Skip to main content