Syntax:
MERGE AS <target_table> AS <target_table_alias> USING AS <source_table> AS <source_table_alias> ON <merge_predicate> WHEN MATCHED [AND <predicate>] --(2 clauses allowed:) THEN <action> --(1 with update; 1 with delete) WHEN NOT MATCHED [BY TARGET] [AND <predicate>] --1 clause allowed THEN INSERT… WHEN NOT MATCHED BY SOURCE [AND <predicate>] --2 clauses allowed THEN <action>; --1 with UPDATE; 1 with DELETE
Example:
DECLARE @tabvar1 TABLE (A TINYINT, B VARCHAR(2)); INSERT INTO @tabvar1 (A, B) VALUES (0, 'AA'),(1, 'BB'),(2, 'CC'); SELECT * FROM @tabvar1; DECLARE @tabvar2 TABLE (A TINYINT, B VARCHAR(2)); INSERT INTO @tabvar2 (A, B) VALUES (0, 'Aa'),(1, 'Bb'),(2, 'Cc'); SELECT * FROM @tabvar2; MERGE INTO @tabvar2 TARGET USING @tabvar1 SOURCE ON target.A = source.A WHEN MATCHED THEN UPDATE SET TARGET.b = SOURCE.b; SELECT * FROM @tabvar1; SELECT * FROM @tabvar2;