https://technet.microsoft.com/en-us/library/bb510625.aspx
Examples
Examples
A. Using MERGE to perform INSERT and UPDATE operations on a table in a single statement
A common scenario is updating one or more columns in a table if a matching row exists, or inserting the data as a new row if a matching row does not exist. This is usually done by passing parameters to a stored procedure that contains the appropriate UPDATE and INSERT statements. With the MERGE statement, you can perform both tasks in a single statement. The following example shows a stored procedure in the AdventureWorks2012database that contains both an INSERT statement and an UPDATE statement. The procedure is then modified to perform the equivalent operations by using a single MERGE statement.
CREATE PROCEDURE dbo.InsertUnitMeasure
@UnitMeasureCode nchar(3),
@Name nvarchar(25)
AS
BEGIN
SET NOCOUNT ON;
-- Update the row if it exists.
UPDATE Production.UnitMeasure
SET Name = @Name
WHERE UnitMeasureCode = @UnitMeasureCode
-- Insert the row if the UPDATE statement failed.
IF (@@ROWCOUNT = 0 )
BEGIN
INSERT INTO Production.UnitMeasure (UnitMeasureCode, Name)
VALUES (@UnitMeasureCode, @Name)
END
END;
GO
-- Test the procedure and return the results.
EXEC InsertUnitMeasure @UnitMeasureCode = 'ABC', @Name = 'Test Value';
SELECT UnitMeasureCode, Name FROM Production.UnitMeasure
WHERE UnitMeasureCode = 'ABC';
GO
-- Rewrite the procedure to perform the same operations using the MERGE statement.
-- Create a temporary table to hold the updated or inserted values from the OUTPUT clause.
CREATE TABLE #MyTempTable
(ExistingCode nchar(3),
ExistingName nvarchar(50),
ExistingDate datetime,
ActionTaken nvarchar(10),
NewCode nchar(3),
NewName nvarchar(50),
NewDate datetime
);
GO
ALTER PROCEDURE dbo.InsertUnitMeasure
@UnitMeasureCode nchar(3),
@Name nvarchar(25)
AS
BEGIN
SET NOCOUNT ON;
MERGE Production.UnitMeasure AS target
USING (SELECT @UnitMeasureCode, @Name) AS source (UnitMeasureCode, Name)
ON (target.UnitMeasureCode = source.UnitMeasureCode)
WHEN MATCHED THEN
UPDATE SET Name = source.Name
WHEN NOT MATCHED THEN
INSERT (UnitMeasureCode, Name)
VALUES (source.UnitMeasureCode, source.Name)
OUTPUT deleted.*, $action, inserted.* INTO #MyTempTable;
END;
GO
-- Test the procedure and return the results.
EXEC InsertUnitMeasure @UnitMeasureCode = 'ABC', @Name = 'New Test Value';
EXEC InsertUnitMeasure @UnitMeasureCode = 'XYZ', @Name = 'Test Value';
EXEC InsertUnitMeasure @UnitMeasureCode = 'ABC', @Name = 'Another Test Value';
SELECT * FROM #MyTempTable;
-- Cleanup
DELETE FROM Production.UnitMeasure WHERE UnitMeasureCode IN ('ABC','XYZ');
DROP TABLE #MyTempTable;
GO
B. Using MERGE to perform UPDATE and DELETE operations on a table in a single statement
The following example uses MERGE to update the ProductInventory table in the AdventureWorks2012 sample database on a daily basis, based on orders that are processed in the SalesOrderDetail table. The Quantity column of the ProductInventory table is updated 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 inventory level of a product to 0 or less, the row for that product is deleted from theProductInventory table.
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'
C. Using MERGE to perform UPDATE and INSERT operations on a target table by using a derived source table
The following example uses MERGE to modify the SalesReason table in the AdventureWorks2012 database by either updating or inserting rows. When the value of NewName in the source table matches a value in the Name column of the target table, (SalesReason), the ReasonType column is updated in the target table. When the value of NewNamedoes not match, the source row is inserted into the target table. The source table is a derived table that uses the Transact-SQL table value constructor to specify multiple rows for the source table. For more information about using the table value constructor in a derived table, see Table Value Constructor (Transact-SQL). The example also shows how to store the results of the OUTPUT clause in a table variable and then summarize the results of the MERGE statment by performing a simple select operation that returns the count of inserted and updated rows.
-- Create a temporary table variable to hold the output actions.
DECLARE @SummaryOfChanges TABLE(Change VARCHAR(20));
MERGE INTO Sales.SalesReason AS Target
USING (VALUES ('Recommendation','Other'), ('Review', 'Marketing'), ('Internet', 'Promotion'))
AS Source (NewName, NewReasonType)
ON Target.Name = Source.NewName
WHEN MATCHED THEN
UPDATE SET ReasonType = Source.NewReasonType
WHEN NOT MATCHED BY TARGET THEN
INSERT (Name, ReasonType) VALUES (NewName, NewReasonType)
OUTPUT $action INTO @SummaryOfChanges;
-- Query the results of the table variable.
SELECT Change, COUNT(*) AS CountPerChange
FROM @SummaryOfChanges
GROUP BY Change;
D. Inserting the results of the MERGE statement into another table
The following example captures data returned from the OUTPUT clause of a MERGE statement and inserts that data into another table. The MERGE statement updates theQuantity column of the ProductInventory table in the AdventureWorks2012 database, based on orders that are processed in the SalesOrderDetail table. The example captures the rows that are updated and inserts them into another table that is used to track inventory changes.
CREATE TABLE Production.UpdatedInventory
(ProductID INT NOT NULL, LocationID int, NewQty int, PreviousQty int,
CONSTRAINT PK_Inventory PRIMARY KEY CLUSTERED (ProductID, LocationID));
GO
INSERT INTO Production.UpdatedInventory
SELECT ProductID, LocationID, NewQty, PreviousQty
FROM
( MERGE Production.ProductInventory AS pi
USING (SELECT ProductID, SUM(OrderQty)
FROM Sales.SalesOrderDetail AS sod
JOIN Sales.SalesOrderHeader AS soh
ON sod.SalesOrderID = soh.SalesOrderID
AND soh.OrderDate BETWEEN '20030701' AND '20030731'
GROUP BY ProductID) AS src (ProductID, OrderQty)
ON pi.ProductID = src.ProductID
WHEN MATCHED AND pi.Quantity - src.OrderQty >= 0
THEN UPDATE SET pi.Quantity = pi.Quantity - src.OrderQty
WHEN MATCHED AND pi.Quantity - src.OrderQty <= 0
THEN DELETE
OUTPUT $action, Inserted.ProductID, Inserted.LocationID, Inserted.Quantity AS NewQty, Deleted.Quantity AS PreviousQty)
AS Changes (Action, ProductID, LocationID, NewQty, PreviousQty) WHERE Action = 'UPDATE';
GO
0 Response to "MERGE (Transact-SQL)"
Post a Comment