Generador de upsert

Upsert en Microsoft SQL Server: MERGE … WITH (HOLDLOCK)

Nombra tu tabla, clave y columnas y obtén la sentencia que Microsoft SQL Server acepta de verdad, con una guarda para que una fila antigua nunca sobrescriba una nueva. Debajo: cómo funciona la mecánica, qué se rompe y cómo Datrise carga Microsoft SQL Server de forma incremental.

Las notas técnicas de esta página están en inglés.

Genera la sentencia

Sentencia · MERGE … WITH (HOLDLOCK)

MERGE [deals] WITH (HOLDLOCK) AS t
USING [deals_staging] AS s
  ON t.[id] = s.[id]
WHEN MATCHED AND s.[updated_at] > t.[updated_at] THEN UPDATE SET
  t.[name] = s.[name],
  t.[stage] = s.[stage],
  t.[amount] = s.[amount],
  t.[owner_id] = s.[owner_id],
  t.[updated_at] = s.[updated_at]
WHEN NOT MATCHED BY TARGET THEN
  INSERT ([id], [name], [stage], [amount], [owner_id], [updated_at])
  VALUES (s.[id], s.[name], s.[stage], s.[amount], s.[owner_id], s.[updated_at]);

Cómo funciona el upsert en Microsoft SQL Server

SQL Server upserts with MERGE. The statement joins the target to a source on the key, updates matched rows and inserts the rest in one pass, and can also delete rows absent from the source with WHEN NOT MATCHED BY SOURCE. The generated version adds a watermark predicate to the MATCHED branch and the HOLDLOCK hint, which serialises the range so two concurrent MERGEs cannot both insert the same key.

MERGE has a reputation for edge-case bugs in older versions, most of them fixed, but the practical rules hold: one row per key in the source, an index on the join columns on both sides, and a terminating semicolon. When the batch is a large fraction of the table, a plain UPDATE followed by INSERT … WHERE NOT EXISTS inside one transaction is often faster than MERGE and easier for the optimizer.

Antes de ejecutarla

MERGE is not atomic against concurrent writers by default: two sessions can both see "not matched" and both insert. WITH (HOLDLOCK) (or SERIALIZABLE) closes that window.
T-SQL requires the MERGE statement to end with a semicolon, and the source should hold one row per key, otherwise you get "The MERGE statement attempted to UPDATE or DELETE the same row more than once".
Add OUTPUT $action, inserted.*, deleted.* to audit what changed, and WHEN NOT MATCHED BY SOURCE THEN DELETE only if the staging table is a full snapshot.

Preguntas frecuentes

Why WITH (HOLDLOCK)?

Without it MERGE reads the target under the default isolation level, so two sessions can both find no match and both insert, raising a duplicate key error on the second. HOLDLOCK takes the range lock up front.

The MERGE statement attempted to UPDATE or DELETE the same row more than once. Why?

The source has two rows with the same key. Dedupe the staging table with ROW_NUMBER() OVER (PARTITION BY key ORDER BY updated_at DESC) = 1 before merging.

How do I see what the MERGE did?

Add OUTPUT $action, inserted.id, deleted.id before the semicolon. $action is INSERT, UPDATE or DELETE per affected row.

El mismo generador para otros destinos

Sáltate escribir el merge

Datrise aterriza entidades de CRM y SaaS en Microsoft SQL Server con esta misma mecánica, una marca de agua sobre updated-at y columnas tipadas, así que la sentencia de arriba es la que corre por ti. Únete a la lista de espera para acceso anticipado.

Explorar el catálogo de integraciones