The MERGE statement performs INSERT/UPDATE/DELETE operations on a target table based on the results of a join with a source table.
DECLARE @Code VARCHAR(3);
DECLARE @Description VARCHAR(15);
SET @Code = 'ABC';
SET @Description ='Merge';
MERGE INTO tblData T
USING (SELECT @Code AS code, @Description AS desc) S
ON T.Code = S.code
WHEN MATCHED THEN
UPDATE SET Description = S.desc
WHEN NOT MATCHED
THEN INSERT VALUES (code, desc);
Here’s a more detailed example to demonstrate how to use the MERGE statement. This script updates the Stock table based on daily trades tracked in the Trades table.
CREATE TABLE Stock (Stock VARCHAR(10) PRIMARY KEY, Qty INT CHECK(Qty > 0));
CREATE TABLE Trades (Stock VARCHAR(10) PRIMARY KEY, Delta INT);
GO
INSERT Stock VALUES ('MSFT', 10);
INSERT Stock VALUES ('TXN', 5);
INSERT Trades VALUES ('MSFT', 5);
INSERT Trades VALUES ('TXN', -5);
INSERT Trades VALUES ('SBUX ', 3);
GO
-- Apply changes to the Stock table based on daily trades
-- tracked in the Trades table. Delete a row from the Stock table
-- if all the stock has been sold. Update the quantity in the Stock
-- table if you still hold some stock after the daily trades. Insert
-- a new row if you acquired a new Stock.
-- As a result, TXN is deleted, SBUX inserted, MSFT updated
MERGE Stock S -- target table
USING Trades T -- source table
ON S.Stock = T.Stock
WHEN MATCHED AND (Qty + Delta = 0) THEN
DELETE -- delete stock if entirely sold
WHEN MATCHED THEN
-- update stock if you still hold some stock
UPDATE SET Qty = Qty + Delta
WHEN NOT MATCHED THEN
-- insert a row if the stock is newly acquired
INSERT VALUES (Stock, Delta)
-- output details of INSERT/UPDATE/DELETE operations
-- made on the target table
OUTPUT $action, inserted.Stock, deleted.Stock;
SELECT * FROM Stock;
GO
