SQL Server OUTPUT CLAUSE

SQL Server OUTPUT clause returns the values of each row that was affected by an INSERT, UPDATE or DELETE  statements.

The Output clause returns a copy of the data that can be inserted into a separate table during the execution of the query.  

This clause is most commonly used  to keep track of data change in table for audit purpose.

OUTPUT clause give access to two virtual tables as given below.

1.  INSERTED that contains the new rows (INSERT or UPDATE)
2.  DELETED that contains the old copy of the rows (empty for INSERT)

The OUTPUT clause can access to all the columns in the table, even those are not part of  Insert statement .

SYNTAX

---- for insert

INSERT INTO tablename 

OUTPUT inserted . *

INTO @output_tablename

VALUES (col_value1 ,col_value2 ,..) 
 
---for delete 

DELETE FROM tablename

OUTPUT deleted.*  
 
INTO @output_tablename WHERE Condition 
 

CREATE TABLE dbo.Item (
ItemId Int identity(1,1),
ItemName Varchar(20),
ItemPurchasedDate Datetime
)

Using Output clause with Insert statement

To insert output clause result into a table, first declare a table variable to store the result of output clause, and use the output clause syntax to store the result into table variable.

To accessing the inserted records, Output clause uses inserted virtual table that contains the new rows.

In following statement, we insert one record in Item table and use the output clause to store those records into a table variable  @insert_log as given below

DECLARE @insert_log TABLE
(ItemId Int, ItemName Varchar(20), ItemPurchasedDate Datetime )

INSERT INTO dbo.Item 
OUTPUT inserted.ItemId, inserted.ItemName, inserted.ItemPurchasedDate
INTO @insert_log
VALUES ('Item1' , Getdate()) 

SELECT * FROM dbo.Item;
SELECT * FROM @insert_log ;

Using Output clause with Update Statement

For accessing updated records, you can use either inserted or deleted virtual table with output clause as per your requirement. 

If you want to access an old values that was before updating the new value then you can use deleted virtual table and if you want to access new updated values then you can use inserted virtual table with Output clause.

In following statement, we update ItemName value in Item table and use the output clause to store the old values for those records into a table variable @update_log.

As we want to access old records so, we use deleted virtual table.

DECLARE @update_log TABLE
( ItemId int, ItemName Varchar(20), ItemPurchasedDate Datetime )

UPDATE dbo.Item SET ItemName ='Item2'
OUTPUT deleted.ItemId, deleted.ItemName, deleted.ItemPurchasedDate
INTO @update_log
WHERE ItemId =1

SELECT * FROM dbo.Item;
SELECT * FROM @update_log

As you can see, here we have three output as follows

1: It is the table output before updating the records.

2: It is the table output after updating the records.

3: It is the output returned by table variable that holds the old records values which was before updating.

If you want to access new records then you can use inserted virtual table in place of deleted.

Output clause with Delete Statement

Following statement uses delete query to delete the record for ItemId= 1 from Item table and store the deleted records into table variable using Output caluse .

To accessing deleted records output clause use deleted  virtual table that contains the old copy of the rows.

DECLARE @delete_log TABLE
( ItemId Int, ItemName Varchar(20), ItemPurchasedDate Datetime )

DELETE FROM dbo.Item 
OUTPUT deleted.ItemId, deleted.ItemName, deleted.ItemPurchasedDate
INTO @delete_log
WHERE ItemId =1

SELECT * FROM dbo.Item;
SELECT * FROM @delete_log

As you can see , There is no data available in Item table as it is deleted but output clause inserts that deleted records into a table variable @delete_log, which is very useful to keeping track of deleted records.

Also Read..

SQL Server Temporal Tables