This article describes how to use the T-SQL MERGE statement in SQL Server database.
MERGE is part of DML statements and uses for insert, update, or delete rows on a target table from the results of a join with a source table.
The insert, update and delete sql commands are used after checking if the join condition is met. The condition is checked using the following clauses: WHEN MATCHED, WHEN NOT MATCHED BY TARGET and WHEN NOT MATCHED BY SOURCE.
MERGE syntax
MERGE table_name_target AS table_alias
USING table_name_source AS table_alias
ON merge_condition
WHEN MATCHED THEN
UPDATE statement
WHEN NOT MATCHED BY TARGET THEN
INSERT statement
WHEN NOT MATCHED BY SOURCE THEN
DELETE statement
GO
MERGE example
MERGE books2 AS TARGET
USING books AS SOURCE
ON SOURCE.id = TARGET.id
WHEN MATCHED THEN
UPDATE SET TARGET.price = SOURCE.price
WHEN NOT MATCHED BY TARGET THEN
INSERT (id, name, price)
VALUES (SOURCE.id, SOURCE.name, SOURCE.price)
WHEN NOT MATCHED BY SOURCE THEN
DELETE
OUTPUT $action,
DELETED.id AS TargetID,
DELETED.name AS TargetName,
DELETED.price AS TargetPrice,
INSERTED.id AS SourceID,
INSERTED.name AS SourceName,
INSERTED.price AS SourcePrice;
SELECT @@ROWCOUNT;
GO