Wednesday, August 10, 2011

How to use Merge T-SQL Command


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