SQL Server 2008 -- The MERGE Statement Demo

• Sample：

USE tempdb GO CREATE TABLE Stock(     Symbol varchar(10) PRIMARY KEY,     Qty int CHECK (Qty > 0)) CREATE TABLE Trade(     Symbol varchar(10) PRIMARY KEY,     Delta int CHECK (Delta <> 0)) INSERT INTO Stock VALUES ('ADVW', 10) INSERT INTO Stock VALUES ('BYA', 5) INSERT INTO Trade VALUES('ADVW', 5) INSERT INTO Trade VALUES('BYA', -5) INSERT INTO Trade VALUES('NWT', 3) SELECT * FROM Stock SELECT * FROM Trade /* The terminating semicolon (part of the SQL standard) is rarely necessary in SQL Server. However, the new MERGE statement in SQL Server 2008 absolutely requires it, and you will receive an error if you omit it. */ MERGE Stock  USING Trade  ON Stock.Symbol = Trade.Symbol  WHEN MATCHED AND (Stock.Qty + Trade.Delta = 0) THEN    -- delete stock if entirely sold    DELETE  WHEN MATCHED THEN    -- update stock quantity (delete takes precedence over update)    UPDATE SET Stock.Qty += Trade.Delta  WHEN NOT MATCHED BY TARGET THEN    -- add newly purchased stock   INSERT VALUES (Trade.Symbol, Trade.Delta) --Using the OUTPUT clause and $action virtual column with MERGE OUTPUT $action, INSERTED.*, DELETED.* ;   DROP TABLE Stock; DROP TABLE Trade;