Compartir a través de

SQL Server 2008 - Merge Capability

  • Artículo

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

image

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

image

3. Use AdventureWorks2008

image

4. Type in or paste in the code from above

5. Select "Query, Execute" from the menu

image

Create the replenishment table

image

image

Steps: Paste them both in

Notice both selects

image

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

image

Remember, the target table is ProductInventory.

Is this what you might do by hand?

853-add inventory
859-add inventory
876-delete
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)

 

image

The replenishment table.

image

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]
  WHEN MATCHED THEN
    DELETE;
Running the merge statement.

image

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.

image 

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

image

Comments