The Output Clause in SQL SERVER

What is Output Clause in SQL SERVER?

OUTPUT clause enables us to get access to recently inserted or deleted rows or records in a table.

The OUTPUT clause can be used with any INSERT, UPDATE, OR DELETE statement.

Let’s understand the OUTPUT Clause using some Scenarios.

OUTPUT clause with Insert Statement

Scenario 1:

There are two tables TestTable and AuditTable. TestTable is the actual table where data should be entered but AuditTable is used for reporting. I want to insert data into TestTable but I want the copy of the data inserted into AuditTable including date and author.

First of all, let’s create both tables

CREATE TABLE TestTable (ID INT, Value VARCHAR(100))

Create Table AuditTable
(	ID INT, Value VARCHAR(100),
	DateOfInsertion Date,
	Author VARCHAR(100)
)

Now enter values in both tables using the OUTPUT clause

INSERT TestTable (ID, Value)
OUTPUT Inserted.ID, Inserted.Value,'2021-1-1','Dabeer Haider' INTO AuditTable
VALUES (1,'FirstVal')
INSERT TestTable (ID, Value)
OUTPUT Inserted.ID, Inserted.Value,'2021-1-1','Dabeer Haider' INTO AuditTable
VALUES (2,'SecondVal')

Let’s check and view the data

SELECT * FROM AuditTable
SELECT * FROM TestTable

Output:

OUTPUT clause with UPDATE or DELETE statement

Scenario 2:

This scenario is the same as scenario 1, but the difference is that it includes the deleted/updated value of the row.

Let’s create both tables

CREATE TABLE TestTable (ID INT, Value VARCHAR(100))
Create Table AuditTable
(	ID INT, 
	Value VARCHAR(100),
	OldID INT,
	OldValue VARCHAR(100),
	DateOfInsertion Date,
	Author VARCHAR(100)
)

Now update values of both records in TestTable

UPDATE TestTable SET Value = 'Some New Value Inserted'
OUTPUT 
	Inserted.ID, Inserted.Value,
	Deleted.ID, Deleted.Value,
	'2021-1-1','Dabeer Haider' 
	INTO AuditTable
WHERE ID IN (1,2)

Now view the data in tables

SELECT * FROM AuditTable
SELECT * FROM TestTable

Output: