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

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?

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.

The replenishment table.

You can copy this merge statement below:

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.