OUTPUT Statement in SQL Server

Introduction

The simplest way to describe the OUTPUT keyword is as a localized trigger, but the OUTPUT keyword here, what we will be able to do inside of an INSERT, an UPDATE, or a DELETE statement is be able to retrieve the values that have been created, the values that have been modified, or the values that have been deleted.

The OUTPUT keyword is fantastic for being able to log what is been changed or that is quite commonly necessary, be able to return the modified data back to the user. Now the way that this will be done is by utilizing, first of all, the OUTPUT keyword and then a couple of special tables that will actually match the target table.

	INSERT Statement	UPDATE Statement	DELETE Statement
INSERTED Table	New Rows	New values	NA
DELETED Table	NA	Original values	Deleted rows

The first is going to the INSERTED table. Now again keep in mind that these will match the target table where the INSERTED table will give us, in the case of an INSERT statement, the brand-new rows that we have created. And by the way, as a real quick side note, if you had what is known as an identity column or an auto count, the inserted table would also give us the value out of there because again, it will be after the row has been inserted so it will know what the value is. In the case of an UPDATE statement, the inserted table will give us whatever new values have been set on the new table. On the flip side we have the DELETED table. Now the most obvious use for the DELETED table is with the DELETE statement. That will of course tell us rows that have been deleted, but in the case of an UPDATE statement, this will give us what the original values are.

Let's have a look at how to utilize an OUTPUT statement with an UPDATE that will give us a greater overview of how both the INSERTED as well as the DELETED table will work.

Create a table, Order table



Insert some record into it.

Insert into [dbo].[Order] values(1,'a',100)
Insert into [dbo].[Order] values(2,'b',1003)
Insert into [dbo].[Order] values(3,'c',10034)
Insert into [dbo].[Order] values(4,'d',1005)
Insert into [dbo].[Order] values(5,'e',1006)
Insert into [dbo].[Order] values(6,'f',1006)
Insert into [dbo].[Order] values(7,'g',1007)
Insert into [dbo].[Order] values(8,'h',1003)
Insert into [dbo].[Order] values(9,'i',1006)
Insert into [dbo].[Order] values(10,'j',1009)
Insert into [dbo].[Order] values(11,'k',1006)
Insert into [dbo].[Order] values(12,'l',1005)
Insert into [dbo].[Order] values(13,'m',1001)

Let's go ahead and execute the following UPDATE statement.

update [dbo].[Order] set Amount = 50
output inserted.Item,
deleted.Amount as 'OldAmount',inserted.Amount as 'NewAmount'
where id < 5

Look at the query result.

 

select *From [dbo].[Order]

Here the table has been updated and look at the OUTPUT result.

Now one little thing that is worth noting here is that if we want to, we can specify that we want to insert those values into another table. For example, if maybe we want to output this to a table, then we just need to simply output a couple of columns, INTO and then the target audit table that we want to place that into.

Create log table (here order_log)

Execute the following query.

update [dbo].[Order] set Amount = 50
output inserted.Item,
deleted.Amount as 'OldAmount',inserted.Amount as 'NewAmount'
INTO order_log
where id < 5

Select the log table.

select * from [dbo].[Order_log]

The big difference between this and a normal UPDATE statement is that, rather than just simply getting the number of rows that have been modified, what we will wind up seeing is the brand-new and old list amounts along with the item name.

Summary

In this article you learned how to display/log the data from the INSERTED table and the DELETED table when executing the update statement. In the same way we can use an OUTPUT statement in insert and delete statements.