Tracking UPDATE changes on SQL Server 2005+ with the OUTPUT clause

It is common to have to execute a mass update on some table, for example, to update a customer credit classification. When such updates are executed, you may want to know what has changed and maybe log it. There are several ways to do it, but i recently found one that makes it easier: the OUTPUT clause.

First, we create a customer table for the example and populate it with some data:

IF OBJECT_ID('Customers') IS NOT NULL
 DROP TABLE Customers
GO

CREATE TABLE Customers (
 CustomerId INT,
 [Name] VARCHAR(30),
 Income MONEY,
 Classification CHAR(1)
)

INSERT INTO Customers (CustomerId, [Name], Income, Classification) VALUES (1, 'JOHN DOE', 5000, 'C')
INSERT INTO Customers (CustomerId, [Name], Income, Classification) VALUES (1, 'JANE DOE', 6000, 'B')
INSERT INTO Customers (CustomerId, [Name], Income, Classification) VALUES (1, 'JOHN SMITH', 6500, 'B')
INSERT INTO Customers (CustomerId, [Name], Income, Classification) VALUES (1, 'HOMELESS JOHN', 100, 'Z')

Now, let's update the data (imagine that there are thousands of rows):

UPDATE Customers
SET Classification = 'C'
OUTPUT DELETED.CustomerId,
    DELETED.[Name],
    DELETED.Classification AS FromClassification,
    INSERTED.Classification AS ToClassification
WHERE Income BETWEEN 5000 AND 7000

The OUTPUT part will cause the following output:

CustomerId  Name                           FromClassification ToClassification
----------- ------------------------------ ------------------ ----------------
1           JOHN DOE                       C                  C
1           JANE DOE                       B                  C
1           JOHN SMITH                     B                  C

If you want to persist the values:

IF OBJECT_ID('CreditChangeLog') IS NOT NULL
 DROP TABLE CreditChangeLog
GO

CREATE TABLE CreditChangeLog (
 CustomerId INT,
 FromClassification CHAR(1),
 ToClassification CHAR(1)
)

UPDATE Customers
SET Classification = 'C'
OUTPUT DELETED.CustomerId,
    DELETED.Classification AS FromClassification,
    INSERTED.Classification AS ToClassification
    INTO CreditChangeLog
WHERE Income BETWEEN 5000 AND 7000

SELECT * FROM CreditChangeLog

This will save the changed values on the CreditChangeLog table.