SQL Server OUTPUT clause: capture rows changed by UPDATE

After a bulk update, @@ROWCOUNT tells you how many rows changed. It does not tell you which rows changed or what their previous values were. SQL Server's OUTPUT clause can return those details as part of the same data modification. Microsoft's OUTPUT documentation defines DELETED as the value before an update and INSERTED as the value after it.

A complete UPDATE and audit example

Run this example in SQL Server. The tables are temporary and the output is stored in #ChangeLog:

CREATE TABLE #Customer (
    CustomerId int PRIMARY KEY,
    Status varchar(20) NOT NULL
);
CREATE TABLE #ChangeLog (
    CustomerId int NOT NULL,
    OldStatus varchar(20) NOT NULL,
    NewStatus varchar(20) NOT NULL
);

INSERT INTO #Customer (CustomerId, Status)
VALUES (1, 'Pending'), (2, 'Pending'), (3, 'Active');

UPDATE c
SET c.Status = 'Active'
OUTPUT INSERTED.CustomerId,
       DELETED.Status,
       INSERTED.Status
INTO #ChangeLog (CustomerId, OldStatus, NewStatus)
FROM #Customer AS c
WHERE c.Status = 'Pending';

SELECT CustomerId, OldStatus, NewStatus
FROM #ChangeLog
ORDER BY CustomerId;

The change log contains customers 1 and 2, each with Pending as the old value and Active as the new value. Customer 3 is absent because the WHERE clause did not select it. An OUTPUT INTO table gives you a set of changed rows to review or persist in a permanent audit table.

Capture the reason and time too

An audit record is more useful when it records why a change happened and who requested it. In a real table, add fields such as ChangeReason, ChangedBy and ChangedAtUtc. Supply a stable request identifier from the application where possible. Review retention and access rules before storing sensitive old values.

If the data modification and the audit record must succeed together, put them in one transaction. OUTPUT INTO is part of the DML statement, but a larger workflow may have additional statements that need the same transaction boundary.

Important limits

• For UPDATE, INSERTED and DELETED are the new and old row versions. For INSERT, only INSERTED applies; for DELETE, only DELETED applies.
• The order of output rows is not guaranteed. Store a key and use ORDER BY when reading the log.
• A plain INSERT ... SELECT cannot reference the SELECT source alias inside OUTPUT to map an old source ID to a generated destination ID. Store a source key in the destination or design a separate, verified mapping step.
• If an update fails or is rolled back, do not treat output returned to the client as committed data.

For an update driven by a lookup table, see the UPDATE FROM JOIN walkthrough. For deciding whether this replaces a row-by-row loop, see SQL Server cursors and alternatives.