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