How to Log Data Changes with the SQL Server OUTPUT Clause

Posted on by Ian

SQL Server has an OUTPUT clause that we can use any time we do an INSERT, UPDATE, DELETE, or MERGE operation. It allows us to retrieve information from modified rows during such operations. This can be especially useful for auditing, logging, or understanding the impact of database changes without needing an additional query.

Let’s look at how the OUTPUT clause can help us log the data changes in our database.

Basic Syntax of the OUTPUT Clause

The OUTPUT clause can be used to return data after performing INSERT, UPDATE, DELETE, or MERGE operations. It returns values from the INSERTED and DELETED tables, which represent the state of the data before and after the operation.

The basic syntax goes something like this:

INSERT INTO TableName (Col1, Col2)
OUTPUT INSERTED.Col1, INSERTED.Col2
VALUES ('Value1', 'Value2');

That’s a simplified version of the syntax. Below is the official syntax specified by Microsoft:

<OUTPUT_CLAUSE> ::=
{
    [ OUTPUT <dml_select_list> INTO { @table_variable | output_table } [ ( column_list ) ] ]
    [ OUTPUT <dml_select_list> ]
}
<dml_select_list> ::=
{ <column_name> | scalar_expression } [ [ AS ] column_alias_identifier ]
    [ , ...n ]

<column_name> ::=
{ DELETED | INSERTED | from_table_name } . { * | column_name }
    | $action

So we have a few options when it comes to logging the output. Let’s explore them.

Example 1: Basic Usage

Here’s an example that returns all data inserted during an INSERT operation:

-- Create a sample table
CREATE TABLE Products (
    ProductID INT IDENTITY, 
    Name NVARCHAR(50), 
    Price DECIMAL(10,2)
    );

-- Insert data and capture the output
INSERT INTO Products (Name, Price)
OUTPUT 
    INSERTED.ProductID, 
    INSERTED.Name,
    INSERTED.Price
VALUES 
    ('Product A', 9.99), 
    ('Product B', 19.99);

Output:

ProductID  Name       Price
---------  ---------  -----
1          Product A  9.99 
2          Product B  19.99

This example returns the newly inserted ProductID, Name, and Price values. This can be handy when we want to see which values were generated for the IDENTITY column, seeing as we didn’t explicitly provide those values.

In this example, we started by creating a table called Products. We’ll use this table for all subsequent examples.

Example 2: Using OUTPUT with INSERT and Table Variables

We can capture the result of an INSERT, UPDATE, DELETE, or MERGE operation in a table variable. Let’s do that with another INSERT operation:

-- Create a table variable to store output
DECLARE @InsertedProducts TABLE (
    ProductID INT, 
    Name NVARCHAR(50), 
    Price DECIMAL(10,2)
    );

-- Insert data and capture the output
INSERT INTO Products (Name, Price)
OUTPUT INSERTED.ProductID, INSERTED.Name, INSERTED.Price INTO @InsertedProducts
VALUES ('Product C', 24.50), ('Product D', 7.00);

-- Display the captured output
SELECT * FROM @InsertedProducts;

Output:

ProductID  Name       Price
---------  ---------  -----
3          Product C  24.5 
4          Product D  7

In this case we captured the newly inserted values into the @InsertedProducts table variable, which can then be used for further operations.

Example 3: Using OUTPUT with UPDATE and Column Aliases

We can also capture both the old and new values during an UPDATE operation, using aliases to name the columns.

-- 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          9.99      10.99

Here, the OUTPUT clause captures both the old and updated prices by using DELETED.Price and INSERTED.Price (along with the ProductID), saving them into the @UpdatedProducts table variable.

Example 4: Using Expressions in OUTPUT

The OUTPUT clause allows the use of scalar expressions. We can compute new values based on old and new data:

-- Capture price difference during the update
DECLARE @PriceDifferences TABLE (
    ProductID INT, 
    PriceChange DECIMAL(10,2)
    );

UPDATE Products
SET Price = Price * 1.20
OUTPUT 
    INSERTED.ProductID, 
    (INSERTED.Price - DELETED.Price) AS PriceChange INTO @PriceDifferences
WHERE Name = 'Product B';

-- Display the result
SELECT * FROM @PriceDifferences;

Output:

ProductID  PriceChange
---------  -----------
2          4

This example calculates and stores the price difference between the old and new values directly in the OUTPUT clause.

Example 5: Using OUTPUT with DELETE

The OUTPUT clause can be used with DELETE operations to retrieve deleted rows.

-- Capture deleted rows
DELETE FROM Products
OUTPUT DELETED.ProductID, DELETED.Name
WHERE Price > 20.00;

Output:

ProductID  Name     
---------  ---------
2          Product B
3          Product C

This query deleted products with a price greater than $20 and output their ProductID and Name.

Example 6: Outputting to a Permanent Table

Instead of using a table variable, we can direct the OUTPUT results into a permanent table:

-- Create a logging table
CREATE TABLE UpdateLog (
    ProductID INT, 
    OldPrice DECIMAL(10,2), 
    NewPrice DECIMAL(10,2), 
    ChangeDate DATETIME
    );

-- Update data and store results in the logging table
UPDATE Products
SET Price = Price * 1.15
OUTPUT 
    DELETED.ProductID, 
    DELETED.Price, 
    INSERTED.Price, 
    GETDATE() INTO UpdateLog;

--Select results from the logging table
SELECT * FROM UpdateLog;

Output:

ProductID  OldPrice  NewPrice  ChangeDate              
---------  --------  --------  ------------------------
1          10.99     12.64     2024-09-18T22:46:38.853Z
4          7         8.05      2024-09-18T22:46:38.853Z

Here, every time an update is performed, the old and new prices, along with the ProductID and the current date, are logged into the UpdateLog table.

Summary of the Features of the OUTPUT Clause

Here’s a quick summary of the various features of the OUTPUT clause:

  1. INSERTED and DELETED:
    • INSERTED: Contains the new values after INSERT or UPDATE.
    • DELETED: Contains the old values before DELETE or UPDATE.
  2. OUTPUT INTO Clause:
    • Allows the results of the OUTPUT clause to be inserted into a table or table variable.
  3. Use of Scalar Expressions:
    • You can perform calculations or operations on values from the INSERTED and DELETED tables within the OUTPUT clause.
  4. Output to Table Variables or Permanent Tables:
    • Capture the output directly into table variables or store them in a permanent logging table for auditing or further use.