TSQL: Capturing Changes with UPDATE and Logging OUTPUT INTO Regular Table, Temp Table, or Table Variable

In T-SQL, you can use the UPDATE statement with the OUTPUT clause to capture the modified rows INTO a table variable, a temporary table, or a physical database table. The OUTPUT clause allows you to return the old and new values of columns affected by the UPDATE statement with the help of Inserted and Deleted tables.

Similar to employing a SQL trigger, developers can utilize the SQL OUTPUT clause syntax to retrieve new data in the Inserted temporary table and previous data in the Deleted temporary table.

In this article, we will demonstrate how you can use the UPDATE statement with the OUTPUT clause to capture the updated rows into a physical table, a temporary table, or a table variable.

Create Employee Data for UPDATE

You can use this Fiddle to follow along and practice executing UPDATE statement along with OUTPUT INTO

Suppose we have a table named Employee with the following structure:

Create Employee table and insert a few sample records

CREATE TABLE Employee (
    EmployeeID INT PRIMARY KEY,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    Salary DECIMAL(10, 2)
);

INSERT INTO Employee (EmployeeID, FirstName, LastName, Salary)
VALUES
    (1, 'John', 'Doe', 50000.00),
    (2, 'Jane', 'Smith', 60000.00),
    (3, 'Michael', 'Johnson', 55000.00);

Employee Table:

EmployeeID	FirstName	LastName	Salary
1	John	Doe	50000.00
2	Jane	Smith	60000.00
3	Michael	Johnson	55000.00

Log UPDATE OUTPUT INTO a Physical Database Table

Now, let's say we want to give a salary raise of 10% to employees whose salary is below $60000, and we also want to log the details of the salary changes in a table named SalaryChangeLog. Here's how you could do it:

Create SalaryChangeLog table, update employee salary, and log updated rows with new and old values

-- Create a physical table to store the salary change log
CREATE TABLE SalaryChangeLog (
	LogID INT IDENTITY(1, 1) PRIMARY KEY
	,EmployeeID INT
	,OldSalary DECIMAL(10, 2)
	,NewSalary DECIMAL(10, 2)
	,ChangeTimestamp DATETIME
	);

-- Perform the salary update with the OUTPUT clause
UPDATE Employee
SET Salary = Salary * 1.10 -- 10% salary raise
OUTPUT Deleted.EmployeeID
	,Deleted.Salary AS OldSalary
	,Inserted.Salary AS NewSalary
	,GETDATE()
INTO SalaryChangeLog
WHERE Salary < 60000.00;

SELECT *
FROM Employee;

SELECT *
FROM SalaryChangeLog;

After executing the UPDATE script:

1. The Employee table will be updated, giving a 10% salary raise to employees (EmployeeID 1 and 3 in our example) with salaries below $60000.

EmployeeID	FirstName	LastName	Salary
1	John	Doe	55000.00
2	Jane	Smith	60000.00
3	Michael	Johnson	60500.00

2. The details of the salary changes will be inserted into the SalaryChangeLog table, including the old and new salaries and a timestamp.

LogID	EmployeeID	OldSalary	NewSalary	ChangeTimestamp
1	1	50000.00	55000.00	2023-08-12 18:54:45.190
2	3	55000.00	60500.00	2023-08-12 18:54:45.190

tip

If you don't have permission to create a physical database table to permanently log UPDATE OUTPUT. You could log the UPDATE and OUTPUT INTO a temporary table or a table variable.

Log UPDATE OUTPUT INTO a Temporary Database Table

Create SalaryChangeLog temporary table, update employee salary, and log updated rows with new and old values

-- Create a temporary table to store the salary change log
CREATE TABLE #SalaryChangeLog (
    LogID INT IDENTITY(1, 1) PRIMARY KEY
    ,EmployeeID INT
    ,OldSalary DECIMAL(10, 2)
    ,NewSalary DECIMAL(10, 2)
    ,ChangeTimestamp DATETIME
    );

-- Perform the salary update with the OUTPUT clause
UPDATE Employee
SET Salary = Salary * 1.10 -- 10% salary raise
OUTPUT Deleted.EmployeeID
    ,Deleted.Salary AS OldSalary
    ,Inserted.Salary AS NewSalary
    ,GETDATE()
INTO #SalaryChangeLog
WHERE Salary < 60000.00;

SELECT *
FROM Employee;

SELECT *
FROM #SalaryChangeLog;

The OUTPUT clause inserts the output rows into a temporary table named #SalaryChangeLog. After the UPDATE statement, you can select from this temporary table to see the captured results.

Log UPDATE OUTPUT INTO a Table Variable

Create SalaryChangeLog table variable, update employee salary, and log updated rows with new and old values

-- Declare a table variable to store the salary change log
DECLARE @SalaryChangeLog TABLE (
    LogID INT IDENTITY(1, 1) PRIMARY KEY
    ,EmployeeID INT
    ,OldSalary DECIMAL(10, 2)
    ,NewSalary DECIMAL(10, 2)
    ,ChangeTimestamp DATETIME
    );

-- Perform the salary update with the OUTPUT clause
UPDATE Employee
SET Salary = Salary * 1.10 -- 10% salary raise
OUTPUT Deleted.EmployeeID
    ,Deleted.Salary AS OldSalary
    ,Inserted.Salary AS NewSalary
    ,GETDATE()
INTO @SalaryChangeLog
WHERE Salary < 60000.00;

SELECT *
FROM Employee;

SELECT *
FROM @SalaryChangeLog

The OUTPUT clause inserts the output rows into a table variable named @SalaryChangeLog. After the UPDATE statement, you can select from this table variable to see the captured results.

UPDATE OUTPUT INTO Summary

In conclusion, leveraging the powerful capabilities of T-SQL's UPDATE statement with the OUTPUT INTO clause opens up new avenues for efficiently managing and tracking data modifications. By seamlessly combining data manipulation with data logging, developers can achieve greater control and insight into their databases. In the example discussed in this blog post, we explored how to update employee's salary while simultaneously capturing these changes in a dedicated log table. This approach not only facilitates accurate record-keeping but also aids in audit trails, analytics, and historical analysis. Whether you're handling transactional systems, auditing trails, or any scenario requiring precise data control, mastering the art of using UPDATE with OUTPUT INTO empowers you to optimize your data manipulation strategies and ensure data integrity throughout your database operations.