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|
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?
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]|
|WHEN MATCHED THEN|
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.