MERGE & Idempotent Upserts

Status: Reviewed (deep-dive v1)
Stack note: PowerShell collectors land rows in staging → warehouse usp_*_Merge must be safe to re-run (no double inventory/backup/alert keys). Blazor doesn’t call merges; Agent/PS does.
Pairs with: SQL-12 (merge procs), SQL-13 (#temp/staging), SQL-15/18 (short tran, lock order), SPIKE-01…08 collectors.
Goal: Idempotent upsert patterns — when MERGE is fine, when UPDATE/INSERT is clearer, and how to avoid double-insert races.

Quick chooser

Need	Prefer
Upsert by natural key from collector	Staging → MERGE or UPDATE then INSERT missing
High concurrency dual writers	Unique key + catch 2601/2627 or serial merge job
Soft “touch” LastSeenAt only	`UPDATE` … `IF @@ROWCOUNT = 0 INSERT`
Delete missing from source	MERGE `WHEN NOT MATCHED BY SOURCE` — careful on partial extracts
Blazor Ack	Not MERGE — single-row UPDATE (SPIKE-07)

Idempotent means: run the same merge twice → same end state, no duplicate keys, no doubled events.

Collector pipeline (estate)

PS collect → staging table or #Stage / TVP
    → usp_*_Merge (set-based)
        → SqlInstance / backup status / agent flags / …

Rules:

[ ] Natural key documented (InstanceName+Host, SqlDatabaseId, AlertType+EntityKey)
[ ] Unique constraint/index on that key before dual collectors exist
[ ] Staging cleared or keyed by RunId so reruns don’t pile junk
[ ] One merge job at a time per target set (or key-range partitions)

Pattern A — UPDATE then INSERT (clear, often best)

CREATE OR ALTER PROC dbo.usp_Inventory_MergeInstances
AS
BEGIN
  SET NOCOUNT ON;
  SET XACT_ABORT ON;

  BEGIN TRY
    BEGIN TRAN;

    UPDATE t
      SET t.HostName = s.HostName,
          t.Version = s.Version,
          t.LastSeenAt = s.LastSeenAt,
          t.IsActive = 1
    FROM dbo.SqlInstance t
    JOIN dbo.SqlInstanceStage s
      ON s.InstanceKey = t.InstanceKey;

    INSERT dbo.SqlInstance (InstanceKey, HostName, InstanceName, Version, LastSeenAt, IsActive)
    SELECT s.InstanceKey, s.HostName, s.InstanceName, s.Version, s.LastSeenAt, 1
    FROM dbo.SqlInstanceStage s
    WHERE NOT EXISTS (
      SELECT 1 FROM dbo.SqlInstance t WHERE t.InstanceKey = s.InstanceKey
    );

    COMMIT TRAN;
  END TRY
  BEGIN CATCH
    IF XACT_STATE() <> 0 ROLLBACK TRAN;
    THROW;
  END CATCH
END;

Race: two sessions both miss then insert → unique violation. Mitigate with unique index + retry once (SQL-18) or single-threaded Agent step.

Pattern B — MERGE (one statement)

MERGE dbo.SqlInstance AS t
USING dbo.SqlInstanceStage AS s
  ON t.InstanceKey = s.InstanceKey
WHEN MATCHED THEN
  UPDATE SET
    t.HostName = s.HostName,
    t.Version = s.Version,
    t.LastSeenAt = s.LastSeenAt,
    t.IsActive = 1
WHEN NOT MATCHED BY TARGET THEN
  INSERT (InstanceKey, HostName, InstanceName, Version, LastSeenAt, IsActive)
  VALUES (s.InstanceKey, s.HostName, s.InstanceName, s.Version, s.LastSeenAt, 1);
-- avoid WHEN NOT MATCHED BY SOURCE DELETE unless source is a full snapshot

Caveats:

MERGE has had correctness edge cases historically — keep actions simple; test under concurrency
Don’t hide complex branching in one MERGE “for cleverness”
HOLDLOCK on target sometimes used to serialize upserts — measure blocking (SQL-18)

MERGE dbo.SqlInstance WITH (HOLDLOCK) AS t
USING …

Pattern C — Alert evaluate style (open/close)

Not classic MERGE-only — dedupe by active unique key (SPIKE-07):

Build #Findings (set-based)
UPDATE existing active alerts (LastSeenAt)
INSERT new keys
CLOSE actives not in findings

Idempotent evaluate: second run updates LastSeen, doesn’t clone Open rows (filtered unique index on active).

Staging tips (PS → SQL)

Approach	Notes
Permanent `*Stage` table	Truncate or delete by `RunId` at start
`#temp` inside merge proc	PS calls proc that bulk-inserts then merges
TVP	Good for modest batches (SQL-13/21)
Bulk insert file	Fast path; then MERGE from staging

Always: set-based load, not row-by-row from PS into final tables.

Double-insert defenses

Unique constraint on business key (non-negotiable)
Single Agent schedule for that merge (or app lock):

EXEC sp_getapplock @Resource = N'MergeSqlInstance', @LockMode = N'Exclusive',
  @LockOwner = N'Transaction', @LockTimeout = 60000;
-- merge…

Catch duplicate key → retry UPDATE path once
Avoid DELETE+INSERT of same key (blows temporal history if SQL-22 enabled)

What not to do

[ ] IF EXISTS pick-one-row in a cursor from PS
[ ] MERGE with WHEN NOT MATCHED BY SOURCE DELETE on a partial estate pull
[ ] No unique key “we’ll be careful”
[ ] Logging an AlertEvent on every merge touch (flood) — only on real state change
[ ] Mega-tran merging millions without batches if boards block (SQL-18)

Common failure patterns

Symptom	Likely cause
PK/UQ violations in job history	Concurrent merges / missing HOLDLOCK/app lock
Duplicates with different surrogate ids	No unique on natural key
Instances vanish	NOT MATCHED BY SOURCE delete + partial stage
Temporal history explodes	Delete+insert instead of UPDATE
Merge “succeeds” but stale	Join predicate wrong / stage not truncated

Tiny lab

Load stage twice; run merge twice — row count stable
Two sessions merge same new key — one UQ fail; add app lock; retest
Partial stage + NOT MATCHED BY SOURCE — see accidental deletes; remove that clause

Tie-ins

SPIKE-01/02/03: Inventory, backup, agent merges
SPIKE-07: Evaluate upsert/close pattern
SQL-15/18: Tran + deadlock around merge vs Ack

Done when

[ ] Each collector target has documented natural key + UQ
[ ] Merge procs idempotent under double run
[ ] Concurrency strategy chosen (serial job vs app lock vs retry)
[ ] This note linked from Knowledge Base list

SQL Dude — SQL-19 MERGE & Idempotent Upserts v1