T-SQL MERGE, OUTPUT and safe upserts

-- Engine: SQL Server 2025 CU9 (17.0.5005.3); database lab with the practice schema. Client: sqlcmd.
-- One MERGE applies the day's stock count to dbo.stock: an update, a delete at zero, an insert, and a row missing from
-- the count. OUTPUT $action ... INTO @changes records what happened to each row; merge_model.py prints the same table.
-- The ROLLBACK at the end keeps the practice data as it was loaded.
SET NOCOUNT ON;
DECLARE @changes TABLE (action nvarchar(10) NOT NULL, sku varchar(12) NOT NULL, old_on_hand int NULL, new_on_hand int NULL);
BEGIN TRANSACTION;
  MERGE dbo.stock AS t
  USING (VALUES ('TEA-250',  N'Assam tea 250 g', 185.00, 52),        -- counted: 52 bags at a new price
                ('GHEE-500', N'Ghee 500 g',      420.00,  0),        -- counted: none left
                ('DAL-1K',   N'Toor dal 1 kg',   160.00, 40))        -- a new item
        AS s (sku, item_name, unit_price, on_hand)
  ON t.sku = s.sku
  WHEN MATCHED AND s.on_hand = 0 THEN
    DELETE                                                          -- GHEE-500: no orders refer to it
  WHEN MATCHED THEN
    UPDATE SET t.on_hand = s.on_hand, t.unit_price = s.unit_price   -- TEA-250: 40 becomes 52
  WHEN NOT MATCHED BY TARGET THEN
    INSERT (sku, item_name, unit_price, on_hand)
    VALUES (s.sku, s.item_name, s.unit_price, s.on_hand)            -- DAL-1K
  WHEN NOT MATCHED BY SOURCE THEN
    UPDATE SET t.on_hand = 0                                        -- RICE-5K was not counted: nothing on hand
  OUTPUT $action, COALESCE(inserted.sku, deleted.sku), deleted.on_hand, inserted.on_hand
    INTO @changes (action, sku, old_on_hand, new_on_hand);         -- the semicolon is required after a MERGE
  SELECT action, sku, old_on_hand, new_on_hand FROM @changes ORDER BY sku;
  -- INSERT DAL-1K NULL 40 | DELETE GHEE-500 0 NULL | UPDATE RICE-5K 25 0 | UPDATE TEA-250 40 52
ROLLBACK TRANSACTION;                                               -- the practice data stays as loaded
SELECT action, COUNT(*) AS rows_affected FROM @changes GROUP BY action ORDER BY action;   -- DELETE 1, INSERT 1, UPDATE 2
GO

"""stock_merge.sql in the lab: sqlcmd runs it as sa in the database lab, on a freshly loaded practice schema."""

from mssql_session import Lab

with Lab() as lab:
    lab.sqlcmd("merge/stock_merge.sql")

$ sqlcmd -S localhost -U sa -C -d lab -i merge/stock_merge.sql
action sku      old_on_hand new_on_hand
------ -------- ----------- -----------
INSERT DAL-1K          NULL          40
DELETE GHEE-500           0        NULL
UPDATE RICE-5K           25           0
UPDATE TEA-250           40          52
action rows_affected
------ -------------
DELETE             1
INSERT             1
UPDATE             2

"""What one MERGE does to each row: a model of stock_merge.sql and the rules on Microsoft's MERGE page.

The clauses are tried in order for every row. A target row that matches a source row is
deleted when the first WHEN MATCHED clause's condition (on_hand = 0) holds, and updated
otherwise; a source row without a target row is inserted; a target row without a source row
gets on_hand 0 (WHEN NOT MATCHED BY SOURCE). Two source rows for one target row stop the
statement with error 8672, and a new key sent twice meets the primary key (error 2627), before
anything changes. Edit COUNT and run it again.
"""

STOCK = {"TEA-250": 40, "RICE-5K": 25, "GHEE-500": 0}  # dbo.stock as loaded: sku -> on_hand

COUNT = [  # the day's count: sku, on_hand
    ("TEA-250", 52),
    ("GHEE-500", 0),
    ("DAL-1K", 40),
]

keys = [sku for sku, _ in COUNT]
duplicates = sorted({sku for sku in keys if keys.count(sku) > 1 and sku in STOCK})
new_twice = sorted({sku for sku in keys if keys.count(sku) > 1 and sku not in STOCK})
if duplicates:
    print(f"error 8672: target row {duplicates[0]} matches more than one source row; nothing changes")
elif new_twice:
    print(f"error 2627: {new_twice[0]} would be inserted twice into the primary key; nothing changes")
else:
    changes = []
    for sku, on_hand in COUNT:
        if sku in STOCK and on_hand == 0:
            changes.append(("DELETE", sku, STOCK[sku], None))
        elif sku in STOCK:
            changes.append(("UPDATE", sku, STOCK[sku], on_hand))
        else:
            changes.append(("INSERT", sku, None, on_hand))
    for sku, on_hand in STOCK.items():
        if sku not in keys:
            changes.append(("UPDATE", sku, on_hand, 0))
    print(f"{'$action':<8} {'sku':<9} {'deleted.on_hand':>15} {'inserted.on_hand':>16}")
    for action, sku, old, new in sorted(changes, key=lambda c: c[1]):
        print(f"{action:<8} {sku:<9} {str(old) if old is not None else 'NULL':>15} {str(new) if new is not None else 'NULL':>16}")
    summary = {a: sum(1 for c in changes if c[0] == a) for a in ("DELETE", "INSERT", "UPDATE")}
    print(", ".join(f"{a} {n}" for a, n in summary.items()))

$action  sku       deleted.on_hand inserted.on_hand
INSERT   DAL-1K               NULL               40
DELETE   GHEE-500                0             NULL
UPDATE   RICE-5K                25                0
UPDATE   TEA-250                40               52
DELETE 1, INSERT 1, UPDATE 2