Overview

• The MERGE statement allows you to write code to determine how to compare two tables and then determine if an INSERT, UPDATE, and/or DELETE operations needs to be performed against one of the tables.
• The MERGE statement can also use the TOP clause to only perform a specific number of operations.
• MERGE performs insert, update, or delete operations on a target table based on the results of a join with a source table.
  • For example, you can synchronize two tables by inserting, updating, or deleting rows in one table based on differences found in the other table.
  • At least one of the three MATCHED clauses must be specified, but they can be specified in any order.
  • A variable cannot be updated more than once in the same MATCHED clause.
• Any insert, update or delete actions specified on the target table by the MERGE statement are limited by any constraints defined on it, including any cascading referential integrity constraints.
  • If IGNORE_DUP_KEY is set to ON for any unique indexes on the target table, MERGE ignores this setting.
  • The MERGE statement requires a semicolon (;) as a statement terminator.
• When used after MERGE, @@ROWCOUNT returns the total number of rows inserted, updated, and deleted to the client.

INSERT/UPDATE in a Single Statement

DECLARE @UnitMeasureCode NCHAR(3) = 'ABC',
    @Name NVARCHAR(25) = 'Test Value';

UPDATE Production.UnitMeasure
SET [Name] = @Name
WHERE UnitMeasureCode = @UnitMeasureCode

-- Insert the row if the UPDATE statement failed.	
IF (@@ROWCOUNT = 0)
BEGIN
	INSERT INTO Production.UnitMeasure (UnitMeasureCode, Name)
	VALUES (@UnitMeasureCode, @Name)
END

INSERT/UPDATE using MERGE

DECLARE @UnitMeasureCode NCHAR(3) = 'ABC',
    @Name NVARCHAR(25) = 'Test Value';

MERGE TOP (100) Production.UnitMeasure AS [Tar]
USING (SELECT @UnitMeasureCode, @Name) AS [Src] ([UnitMeasureCode], [Name])
ON ([Tar].UnitMeasureCode = [Src].UnitMeasureCode)
WHEN MATCHED THEN
	UPDATE
		SET [Name] = [Src].[Name]
WHEN NOT MATCHED THEN
	INSERT 
	([UnitMeasureCode], [Name])
	VALUES ([Src].UnitMeasureCode, [Src].[Name])
WHEN NOT MATCHED BY SOURCE THEN
   DELETE;