When we run an UPDATE statement in SQL, we might not always be interested in which rows or values were updated. But there may be times when we need to examine this data, or log it somewhere like in a separate table.

This is where SQL Server’s OUTPUT clause can help tremendously.

In SQL Server, OUTPUT is an optional clause that returns data affected during INSERT, UPDATE, DELETE, or MERGE operations. We can therefore use it to return the data affected during our UPDATE operation.

How it Works

When we use the OUTPUT clause with the UPDATE statement, we can return both the old value for each column and the new one. We do this by prefixing the column name with either DELETED or INSERTED:

• DELETED: Used to return the old value (i.e. the value that is being updated).
• INSERTED: Used to return the new value (i.e. the value that replaces the old value).

The syntax goes like this:

DELETED.column_name
INSERTED.column_name

So it’s a bit like when we qualify a column’s name with the table name, except we use DELETED or INSERTED, depending on which value we require.

We can use both in the same OUTPUT clause in order to get both the old value and the new value.

Example

Here’s an example that uses the OUTPUT clause to return the values that were updated by an UPDATE statement:

UPDATE Products
SET Price = Price * 1.10
OUTPUT 
    INSERTED.ProductID AS ProductID,
    DELETED.Price AS OldPrice, 
    INSERTED.Price AS NewPrice
WHERE Name = 'Product A';

Example output:

ProductID  OldPrice  NewPrice
---------  --------  --------
1          9.99      10.99   

Here, the OUTPUT clause returns both the old and updated prices by using DELETED.Price and INSERTED.Price (along with the ProductID).

Save the Results

We can also save the result to a table or table variable. Here’s an example of saving the results to a table variable:

-- Create a table variable to store output
DECLARE @UpdatedProducts TABLE (
    ProductID INT, 
    OldPrice DECIMAL(10,2), 
    NewPrice DECIMAL(10,2)
    );

-- Update data and capture old and new values
UPDATE Products
SET Price = Price * 1.10
OUTPUT 
    INSERTED.ProductID AS ProductID,
    DELETED.Price AS OldPrice, 
    INSERTED.Price AS NewPrice INTO @UpdatedProducts
WHERE Name = 'Product A';

-- Display the output
SELECT * FROM @UpdatedProducts;

Output:

ProductID  OldPrice  NewPrice
---------  --------  --------
1          10.99     12.09   

Here, I used the OUTPUT ... INTO syntax to save the results to the @UpdatedProducts table variable.

After that, I merely selected the results, but the variable could be used for further processing as required.