SQL Server - Merge clause example with description

sql-server-merge-poc.sql

-- Declare `@SummaryOfChanges` as variable table, disappear when DB connection session end.
-- Use for storing changes which persisted by transaction.
DECLARE @SummaryOfChanges TABLE(Change VARCHAR(20), id VARCHAR(5), date VARCHAR(8), val INT );

-- Target table is the initial resource of values comparison and changing target of decision of `ON` clauses.
MERGE dbo.[T_target_table] AS T
-- Source value is the input of ours, e.g. input from `user`, `form`, `service`.
USING (VALUES ('97', '2018-03-01')) AS S (src_id, src_date)
-- Condition to find out the fact of data, result of comparison will use for action decision.
ON T.id = S.src_id AND T.date = S.src_date
-- Action defined when you found the row.
WHEN MATCHED THEN 
    UPDATE SET T.target_val = 4700000
-- Action defined when you not found the row.
WHEN NOT MATCHED BY TARGET THEN 
    INSERT (id, date, target_val) VALUES (S.src_id, S.src_date, 5000000)
-- `OUTPUT` keyword is the result of the actions performed by all of the `MERGE` statement.
-- `$action` is a parameter which be filled value after each decision perform.
-- `Inserted` is and object which be filled value by `INSERT` action in `WHEN NOT MATCHED` clause is performed.
-- There are also have other object than `Inserted`,
--     see https://docs.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql?view=sql-server-2017
-- `INTO` is something like `SELECT ... INTO` clause.
OUTPUT $action, Inserted.id, Inserted.date, Inserted.val INTO @SummaryOfChanges;

-- Query all the changes occur of all `MERGE` statement.
SELECT * FROM @SummaryOfChanges;