MERGE (Transact‑SQL)

The MERGE statement provides a way to perform INSERT, UPDATE, or DELETE operations on a target table based on the results of joining it with a source table.

Syntax

MERGE [TOP (expression) [PERCENT]] [INTO] target_table [WITH (  [ ,...n ] )]
    USING source_table
    ON 
    [ WHEN MATCHED [ AND  ] THEN  ]
    [ WHEN NOT MATCHED [ BY TARGET ] [ AND  ] THEN  ]
    [ WHEN NOT MATCHED BY SOURCE [ AND  ] THEN  ]
    [ OPTION (  [ ,...n ] ) ];

Parameters

  • target_table – The table that will receive the changes.
  • source_table – The table that provides the rows for comparison.
  • search_condition – The join predicate that matches source rows to target rows.
  • matched_action – UPDATE or DELETE operation when a source row matches a target row.
  • not_matched_action – INSERT operation when a source row does not match any target row.
  • not_matched_by_source_action – DELETE operation when a target row has no matching source row.

Example

Synchronize the Sales.Target table with the Sales.Staging table.

MERGE INTO Sales.Target AS T
USING Sales.Staging AS S
ON T.ProductID = S.ProductID
WHEN MATCHED THEN
    UPDATE SET T.Quantity = S.Quantity, T.Price = S.Price
WHEN NOT MATCHED BY TARGET THEN
    INSERT (ProductID, Quantity, Price)
    VALUES (S.ProductID, S.Quantity, S.Price)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

See Also