Introduction to OUTPUT Statement in SQL
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.
Latest posts by Tom Mohan (see all)
- Deep dive into ASP.NET Core Middleware - July 26, 2017
- How to choose a WCF binding ? - January 29, 2017
- Get free Azure trial worth ₹50,000 and Azure webinar series . - September 3, 2016