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:

Support us by sharing this post