Bits of .NET

After MERGE, don’t know what was inserted vs updated? OUTPUT clause captures the changes.

MERGE with OUTPUT:

MERGE INTO Products AS target
USING @NewProducts AS source
ON target.ProductId = source.ProductId
WHEN MATCHED THEN
    UPDATE SET Price = source.Price
WHEN NOT MATCHED THEN
    INSERT (ProductId, Name, Price)
    VALUES (source.ProductId, source.Name, source.Price)
OUTPUT 
    $action AS Action,
    INSERTED.ProductId,
    INSERTED.Name,
    DELETED.Price AS OldPrice,
    INSERTED.Price AS NewPrice
INTO @ChangeLog;

SELECT * FROM @ChangeLog;

Output Shows:

Action    ProductId  Name      OldPrice  NewPrice
INSERT    101        Widget    NULL      29.99
UPDATE    102        Gadget    19.99     24.99
INSERT    103        Tool      NULL      39.99

Use Cases: Audit logging, change tracking, debugging imports