Insert Over DML - Merge, Using, Matched, Output, Action

DROP TABLE Stocks
DROP TABLE DailyTradeUpdates
DROP TABLE StockPriceEvolution

CREATE TABLE Stocks(StocksName varchar(100), Qty int, Price dec(10,2))

CREATE TABLE DailyTradeUpdates(StocksName varchar(100), Delta int, Price dec(10,2))

CREATE TABLE StockPriceEvolution(StocksName varchar(100), Price dec(10,2), TradingDate date)


INSERT Stocks VALUES('MSFT',100, 36.67), ('SUN',100, 26.67)

INSERT DailyTradeUpdates  VALUES('MSFT',20, 35.67), ('SUN',10, -6.67), ('GOO',100, 66.67)

SELECT * FROM Stocks

StocksName Qty Price
MSFT 100 36.67
SUN 100 26.67

SELECT * FROM DailyTradeUpdates

StocksName Delta Price
MSFT 20 35.67
SUN 10 -6.67
GOO 100 66.67

MERGE Stocks AS s
USING DailyTradeUpdates AS d ON s.StocksName = d.StocksName and Qty!=0
WHEN MATCHED THEN
--DELETE
UPDATE SET Price = d.Price, Qty+=d.Delta
WHEN NOT MATCHED THEN
INSERT VALUES(d.StocksName, d.Price, d.Delta)
--INSERT (StocksName, Price, Qty) VALUES (d.StocksName, d.Price, d.Delta)
OUTPUT $action, d.StocksName, d.Price;

SELECT * FROM Stocks
StocksName Qty Price
MSFT 120 35.67
SUN 110 -6.67
GOO 66 100.00

INSERT INTO StockPriceEvolution(StocksName, Price, TradingDate)
SELECT StocksName, Price, CONVERT(DATE, SYSDATETIME())
FROM
(
MERGE Stocks AS s
USING DailyTradeUpdates AS d ON s.StocksName = d.StocksName and Qty!=0
WHEN MATCHED THEN
UPDATE SET Price = d.Price, Qty+=d.Delta
WHEN NOT MATCHED THEN
INSERT VALUES(d.StocksName, d.Price, d.Delta)
OUTPUT $action, d.StocksName, d.Price
) Updates(Action, StocksName, Price)
WHERE Action='UPDATE' OR Action='INSERT'

StockPriceEvolution
StocksName Price TradingDate
GOO 66.67 2015-02-11
MSFT 35.67 2015-02-11
SUN -6.67 2015-02-11

https://msdn.microsoft.com/en-us/library/bb510625.aspx
https://msdn.microsoft.com/en-us/library/ms177564.aspx