SQL Server -How to use OUTPUT Statement

facebooktwittergoogle_plusredditpinterestlinkedinmail

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

OUTPUT Statement -1

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.

OUTPUT Statement - 2

SELECT *FROM [dbo].[ORDER]

OUTPUT Statement - 3

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)

OUTPUT Statement -4

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]

OUTPUT Statement - 5

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.

The following two tabs change content below.

Tom Mohan

Tom Mohan is a technologist who loves to code and build. He enjoys working on Microsoft Technologies. Tom specializes in ASP.NET MVC, Web API , Azure, C# ,WPF, SQL etc and holds a Bachelor engineering degree in Computer Science. Certification : MCSD , MCTS
  • Ηello, Neat post. There’s an іssuе with your
    ԝeb site in intеrnet explorer, might checҟ this… IE nonetheless is tthe mɑrkеtрlace chief
    and a big part of people wіll miss your magnifіcent writing because of this problem.

    Heгe iis my ebsite … check over here (ibtimes.com)

    • Tom Mohan

      Yes. Some problem is there in old IE. It is working fine with IE11. Thanks for your valuable information.