Upsert generator

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

Name your table, key and columns and get the statement Microsoft SQL Server actually accepts, with a guard so an older row never overwrites a newer one. Below it: how the mechanic works, what breaks, and how Datrise loads Microsoft SQL Server incrementally.

Generate the statement

Statement · 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]);

How the upsert works in 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.

Before you run it

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.

Questions people ask

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.

The same generator for other destinations

Skip writing the merge at all

Datrise lands CRM and SaaS entities into Microsoft SQL Server with this exact mechanic, a watermark on updated-at, and typed columns, so the statement above is what runs on your behalf. Join the waitlist to get early access.

Browse the integration catalog