SQL Server 2008 MERGE Statement

MERGE Statement

What do you do when your application logic requires that you INSERT a record if the record doesn’t exist, or UPDATE the record if it does exist? In SQL Server 2008, It has been simplified the amount of T-SQL code it requires to perform INSERT/UPDATE logic, by implementing the MERGE statement.

You can do more with the MERGE statement then just inserting and updating records as mentioned above. You can also use it to delete records. Another thing you can do with the MERGE statement is to perform a specific number of merge actions based on a TOP clause. The MERGE statement can also generate output that will identify which records where inserted, updated, or deleted.

Following is an example of using MERGE to perform UPDATE and DELETE operations on a table in a single statement

Here I am using AdventureWorks sample database to show this example.

MERGE can be used to update the ProductInventory table in the AdventureWorks sample database on a daily basis, based on orders that are processed in the SalesOrderDetail table. The following MERGE statement updates the Quantity column of the ProductInventory table by subtracting the number of orders placed each day for each product in the SalesOrderDetail table. If the number of orders for a product drops the product's inventory to 0, the row for that product is deleted from the ProductInventory table.

USE AdventureWorks;

GO

IF OBJECT_ID (N'Production.usp_UpdateInventory', N'P') IS NOT NULL DROP PROCEDURE Production.usp_UpdateInventory;

GO

CREATE PROCEDURE Production.usp_UpdateInventory

@OrderDate datetime

AS

MERGE Production.ProductInventory AS target

USING (SELECT ProductID, SUM(OrderQty) FROM Sales.SalesOrderDetail AS sod

JOIN Sales.SalesOrderHeader AS soh

ON sod.SalesOrderID = soh.SalesOrderID

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;

GO

EXECUTE Production.usp_UpdateInventory '20030501'

Advantages of using MERGE statement

1 - With a single statement we can easily implement insert, update, and delete logic to handle criteria for maintaining a table.

2 - The MERGE statement handles all the joining of the source and target. This minimizes the amount of code you need to write to handle merge logic.

3 – It requires less SQL Server resources which make it fast and optimized.