MERGE

MERGE#

Performs INSERT, UPDATE and DELETE operations on a target table or view in a single statement, based on the result of a join with a source.

Syntax#

merge ::=

MERGE INTO target AS alias USING source ON search condition WHEN MATCHED AND condition THEN UPDATEUPDATE SETSET assignmentsassignments UPDATEUPDATE SETSET assignmentsassignments DELETEDELETE UPDATEUPDATE SETSET assignmentsassignments UPDATEUPDATE SETSET assignmentsassignments DELETEDELETE WHEN NOT MATCHED BY TARGET AND condition THEN INSERT ( columns ) VALUES ( values ) DEFAULT VALUES WHEN NOT MATCHED BY SOURCE AND condition THEN UPDATEUPDATE SETSET assignmentsassignments UPDATEUPDATE SETSET assignmentsassignments DELETEDELETE UPDATEUPDATE SETSET assignmentsassignments UPDATEUPDATE SETSET assignmentsassignments DELETEDELETE

Click an element in the diagram to drill down to its definition.

Arguments#

target

The table or view whose rows are inserted, updated or deleted. INTO is optional.

USING source

A table, view or subquery that supplies the rows to compare against the target.

ON search condition

The condition that determines which target rows match which source rows.

WHEN MATCHED THEN

Runs for target rows that have a matching source row. The action is UPDATE SET or DELETE.

WHEN NOT MATCHED [BY TARGET] THEN

Runs for source rows with no matching target row. The action is INSERT.

WHEN NOT MATCHED BY SOURCE THEN

Runs for target rows with no matching source row. The action is UPDATE SET or DELETE.

Each WHEN clause may be further restricted with an AND condition, and several WHEN clauses may be combined.

Examples#

Upsert — update rows that already exist, insert those that don’t, and delete target rows that are no longer in the source:

MERGE INTO dbo.Product AS t
USING staging.Product AS s
   ON t.Id = s.Id
WHEN MATCHED THEN
    UPDATE SET t.Name = s.Name, t.Price = s.Price
WHEN NOT MATCHED BY TARGET THEN
    INSERT (Id, Name, Price) VALUES (s.Id, s.Name, s.Price)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

See also