MERGE: Compare a source to a target table

A load can bring rows that we must insert when they are new and update when they already exist. MERGE expresses that comparison using a source—the incoming set—a target—the table that receives changes—and several conditional actions.

The syntax concentrates the intent but also combines several operations in one statement. We use it when it genuinely improves clarity and after testing concurrency—simultaneous executions over the same data—constraints, triggers, and volume.

General form

MERGE INTO dbo.TablaDestino AS destino
USING origen
    ON destino.Clave = origen.Clave
WHEN MATCHED THEN
    UPDATE SET Columna = origen.Columna
WHEN NOT MATCHED BY TARGET THEN
    INSERT (Clave, Columna)
    VALUES (origen.Clave, origen.Columna)
OUTPUT
    $action,
    inserted.Clave;

The final semicolon is required. The ON condition states only how source and destination are matched; the filters for each action are written in its WHEN clause.

Synchronize a small load

DECLARE @Categorias TABLE
(
    IdCategoria SMALLINT NULL,
    Nombre NVARCHAR(80) NOT NULL
);

INSERT INTO @Categorias (IdCategoria, Nombre)
VALUES
    (1, N'Accesorios y conectividad'),
    (NULL, N'Liquidación');

MERGE INTO dbo.Categoria AS destino
USING @Categorias AS origen
    ON destino.IdCategoria = origen.IdCategoria
WHEN MATCHED
     AND destino.Nombre <> origen.Nombre THEN
    UPDATE SET
        Nombre = origen.Nombre
WHEN NOT MATCHED BY TARGET THEN
    INSERT (Nombre)
    VALUES
    (
        origen.Nombre
    )
OUTPUT
    $action AS Accion,
    inserted.IdCategoria,
    deleted.Nombre AS NombreAnterior,
    inserted.Nombre AS NombreNuevo;

$action` indicates whether each row was inserted, updated, or deleted. The output has no guaranteed order.

The source value NULL, which represents absent or unknown data, is not inserted into the IDENTITY column. It only causes that row not to find a match, so SQL Server generates a new key. An IDENTITY column automatically produces a number for each new row. The load contract must define precisely what a missing key means.

Font must be consistent

If more than one row in the source matches the same destination row and the action attempts to update it, SQL Server fails. We normalize or reject duplicates before running the sync.

The comparison key is the value or set of values that identifies a match. It must be protected according to the business rule, usually by a unique constraint or index, which prevents that key from being repeated. Without this protection, the logic may appear correct and still produce duplicates under concurrency.

Do not add improper filters to `ON

-- Evitar usar el ON para ocultar filas del destino.
ON destino.IdProducto = origen.IdProducto
AND destino.Activo = 1
```

That condition can cause an existing row to be treated as unmatched and lead to unexpected results. We keep the matching columns in `ON` and place additional conditions in the appropriate `WHEN`.

## When to prefer separate instructions

If we only need to update from another table, or if the logic is better understood as independent steps, separate `UPDATE`, `INSERT`, and `DELETE` may be clearer and more scalable. They also make it simple to isolate plans, errors and effects of each operation.

We do not include `WHEN NOT MATCHED BY SOURCE THEN DELETE` out of reflex. A partial source does not necessarily represent the entire universe and could eliminate valid rows.

## What should be checked

We test with duplicates, concurrent executions, constraints, triggers, intermediate errors, and representative volumes. We check indexes on the join keys and capture `OUTPUT` when we need to audit the actions.

`MERGE` does not replace an idempotent load design—an operation that can be repeated without accumulating improper changes—or transactional control, which commits or rolls back a unit of work. It is an available construct, not a requirement for every synchronization.

## Related to

- [`OUTPUT`: identifies the action and the affected values.](/en/reference/output-inserted-deleted/)
- [`EXCEPT`: Helps explore differences before modifying data.](/en/reference/intersect-except/)
- [Transactions: group synchronization and its validations.](/en/reference/transactions-try-catch/)
- [Unique indexes: protect the matching rule under concurrency.](/en/development/inspect-primary-keys-unique-constraints-and-indexes/)