The MERGE statement performs insert, update, or delete operations on a target
table based on the results of a join to a source table.
For example, you can synchronize two tables by inserting, updating, or deleting
all of the rows in one table based on differences in the other table.
Here is an example of MERGE code:
MERGE MyDatabase.Inventory AS target
USING (SELECT ProductID, SUM(OrderQty) FROM mySales.Orders AS o
JOIN mySales.mySalesOrderHeader AS soh
ON o.mySalesOrderID = soh.mySalesOrderID
AND soh.OrderDate = @OrderDate
GROUP BY ProductID) AS source (ProductID, OrderQty)
ON (target.ProductID = source.ProductID)
WHEN MATCHED AND target.Quantity - source.OrderQty <= 0
THEN DELETE
WHEN MATCHED
THEN UPDATE SET target.Quantity = target.Quantity - source.OrderQty,
target.ModifiedDate = GETDATE()
OUTPUT $action, Inserted.ProductID, Inserted.Quantity, Inserted.ModifiedDate,
Deleted.ProductID,
Deleted.Quantity, Deleted.ModifiedDate;
