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.
See my blog about installing adventureworks2008. It wasn't trivial to me.
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
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.
Remember, the target table is ProductInventory.
Is this what you might do by hand?
For product 853, we are getting 250 more.
For product 859, we are getting 500 more.
For product 876, nothing came back.
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.
PingBack from http://www.tmao.info/sql-server-2008-merge-capability/
Thank you for submitting this cool story - Trackback from DotNetShoutout