Just a Scenarion, maybe it helps you to understand what gordatron and i were talking about:
Following Situation:
There is a Table "Products" wich is central storage place for product informations
and a table "UpdatedProducts" which structure looks exactly like "Products" table but data
maybe different. Think of following scenarion: you export product table to excel in the morning. the whole
day you delete, add, update products in your excel table. At the end of the day you want to re-import your excel
data to "Products" table. What you need:
- delete all records from "UpdatedProducts"
- insert data from excel to
"UpdatedProducts" (bulk insert if possible)
- update the "Products"
table
Then a Merge-Statement could look like this:
MERGE Products AS TARGET
USING UpdatedProducts AS SOURCE
ON TARGET.ProductID = SOURCE.ProductID
WHEN MATCHED AND TARGET.ProductName <> SOURCE.ProductName OR TARGET.Rate <> SOURCE.Rate
THEN UPDATE SET TARGET.ProductName = SOURCE.ProductName,
TARGET.Rate = SOURCE.Rate
WHEN NOT MATCHED BY TARGET
THEN INSERT (ProductID, ProductName, Rate)
VALUES (SOURCE.ProductID, SOURCE.ProductName, SOURCE.Rate)
WHEN NOT MATCHED BY SOURCE
THEN DELETE
What this Statement does:
WHEN MATCHED:
Data exist in both tables, we update data in "Products" if ProductName or Rate is different
WHEN NOT MATCHED BY TARGET:
Data exist in staging table but not in your original table, we add them to "Products"
WHEN NOT MATCHED BY SOURCE:
Data exists in your original table but not in staging table, thy will be deleted from "Products"
Thanks a lot to http://www.mssqltips.com/sqlservertip/1704/using-merge-in-sql-server-to-insert-update-and-delete-at-the-same-time/ for this perfect example!
