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.
- INSERTED that contains the new rows (INSERT or UPDATE)
- 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 .
---- 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 ;
As you can see the output of Item table and table variable, While inserting new records into item table, ItemId is not part of the insert statement . However, you can access to the ItemId column in the OUTPUT clause.
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.