merge example with sql server

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