Gerador de upsert

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

Nomeie tabela, chave e colunas e receba a instrução que o Microsoft SQL Server realmente aceita, com uma guarda para que uma linha antiga nunca sobrescreva uma nova. Abaixo: como a mecânica funciona, o que quebra e como a Datrise carrega o Microsoft SQL Server de forma incremental.

As notas técnicas desta página estão em inglês.

Gere a instrução

Instrução · 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]);

Como o upsert funciona no 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 executar

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.

Perguntas frequentes

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.

O mesmo gerador para outros destinos

Pule a escrita do merge

A Datrise entrega entidades de CRM e SaaS no Microsoft SQL Server com exatamente esta mecânica, uma marca d'água sobre updated-at e colunas tipadas, então a instrução acima é a que roda por você. Entre na lista de espera para acesso antecipado.

Explorar o catálogo de integrações