Uses of OUTPUT clause in SQL Server

The output clause introduced in SQL Server 2005 is handy and it simplifies the coding for different scenarios. It returns inserted, deleted and updated records to the client application.

Employee and employee log tables are created for this demonstration. The requirement is to log any changes such as adding new records or removing, modifying existing records in employee table to employee log table.

CREATE TABLE dbo.Employee (
	EmpId INT NOT NULL IDENTITY(1,1), 
	EmpDeptId INT NOT NULL,
	Empname VARCHAR(50) NOT NULL,
	EmpPreferredName VARCHAR(20) NOT NULL,
	Empdoj DATE NOT NULL
);
CREATE TABLE dbo.EmployeeLog (
	EmpId INT NOT NULL, 
	EmpDeptId INT NOT NULL,
	Empname VARCHAR(50) NOT NULL,
	Empdoj DATE NOT NULL,
	LogDtTm DATETIME NOT NULL DEFAULT GETDATE(),
	ActionType CHAR(1)
) 

OUTPUT clause in insert statement

Using OUTPUT clause, records inserted into a table can be returned directly to client application or it can be stored in to a target table.

The following query will insert record into employee table and inserted records are returned to client with the use of OUTPUT clause.

The temporary in-memory tables such as INSERTED and DELETED can be accessed only within Trigger before the implementation of OUTPUT clause in SQL Server. With the support of OUTPUT clause these temporary tables are available and accessible outside of the trigger.

INSERT INTO dbo.Employee (EmpDeptId,Empname,EmpPreferredName, Empdoj)
OUTPUT INSERTED.*
VALUES
(1,'Kayla Woodcock','Kayla','2015-12-23'),
(1,'Hudson Onslow','Hudson','2015-12-22'),
(1,'Isabella Rupp','Isabella','2015-12-22'),
(1,'Eva Muirden','Eva','2015-12-20'),
(1,'Sophia Hinton','Sophia','2015-12-22'),
(1,'Amy Trefl','Amy','2015-12-18'),
(1,'Anthony Grosse','Anthony','2015-12-23'),
(1,'Alica Fatnowna','Alica','2015-12-23'),
(1,'Stella Rosenhain','Stella','2015-12-25'),
(1,'Ethan Onslow','Ethan','2015-12-21')

The execution of above statement returns following output, since INSERTED.* is specified beside the OUTPUT keyword, it returns all the columns for the affected rows. We can also specify the column name explicitly if all the column values are not required.
OUTPUT INSERTED.Empname, INSERTED.EmpDeptId

Storing inserted records in log table

The records inserted using below statement is stored into the log table, the columns name is explicitly specified because the table structure is not identical. If the table structure is same, the statement is even more simple, similar to this statement “OUTPUT INSERTED.* INTO EmployeeLog”

INSERT INTO dbo.Employee (EmpDeptId,Empname,EmpPreferredName, Empdoj)
OUTPUT INSERTED.EmpId, INSERTED.EmpDeptId, INSERTED.Empname, INSERTED.Empdoj, 'I'
INTO dbo.EmployeeLog (EmpId,EmpDeptId,Empname,Empdoj,ActionType)
VALUES
(1,'Kayla Woodcock','Kayla','2015-12-23'),
(1,'Hudson Onslow','Hudson','2015-12-22'),
(1,'Isabella Rupp','Isabella','2015-12-22'),
(1,'Eva Muirden','Eva','2015-12-20'),
(1,'Sophia Hinton','Sophia','2015-12-22'),
(1,'Amy Trefl','Amy','2015-12-18'),
(1,'Anthony Grosse','Anthony','2015-12-23'),
(1,'Alica Fatnowna','Alica','2015-12-23'),
(1,'Stella Rosenhain','Stella','2015-12-25'),
(1,'Ethan Onslow','Ethan','2015-12-21')

The following screen scrap shows the that the records inserted in employee table also inserted into the employee log table.

Tracking updates

In the following query the department id of the two employees will be changed to 2 and the changed records are logged into employee log table.

UPDATE dbo.Employee
SET EmpDeptId = 2
OUTPUT INSERTED.EmpId, INSERTED.EmpDeptId, INSERTED.Empname, INSERTED.Empdoj, 'U' 
INTO dbo.EmployeeLog (EmpId,EmpDeptId,Empname,Empdoj,ActionType)
WHERE Empname IN ('Kayla Woodcock','Stella Rosenhain')

Let’s select the two employee’s records from both tables.

SELECT * FROM dbo.Employee WHERE Empname IN ('Kayla Woodcock','Stella Rosenhain')
SELECT * FROM dbo.EmployeeLog WHERE Empname IN ('Kayla Woodcock','Stella Rosenhain')

As shown in the following image, department id for the two employees were updated and the same is logged in log table. It is easy to track the changes using the log table as the date and time of the changes are logged along with the action type which are I, U, D Insert, Update and Delete respectively.

Tracking DELETE

Similar to update and insert the delete action can also be logged using OUTPUT clause. But for this DELETED temporary table is used as it holds all the deleted records of the transactional statement.

DELETE dbo.Employee
OUTPUT DELETED.EmpId, DELETED.EmpDeptId, DELETED.Empname, DELETED.Empdoj, 'D'
INTO dbo.EmployeeLog (EmpId,EmpDeptId,Empname,Empdoj,ActionType)
WHERE Empname IN ('Stella Rosenhain')

SELECT * FROM dbo.Employee WHERE Empname IN ('Stella Rosenhain')
SELECT * FROM dbo.EmployeeLog WHERE Empname IN ('Stella Rosenhain')

The following screen scrap shows that the record is deleted and at the same time it is logged into the employee log table.

$action

Throughout these examples I have used action type and the value is hard-coded as I or U or D for insert, update and delete actions respectively. The action type can be identified using the keyword $action but it is only supported in MERGE statement.