MERGE

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;