When processing complex insert, update and delete statements it’s often useful to know exactly what has been changed, whether it’s for auditing purposes, or to return a value to a calling application (such as an ID, or other value following an insert, for example). The OUTPUT clause in T-SQL allows you to do just that, and it’s really, really straightforward….I’ll show you how!

Let’s start by creating a new table:

if object_id('tempdb..#outputexample') is not null drop table #outputexample;
go
create table #outputexample
(
       WidgetID int identity(1,1),
       WidgetName varchar(200),
       WidgetStatus varchar(10)
)

We will begin by populating this with a single row, and using the OUTPUT clause to return details on the row added:

insert #outputexample
output inserted.*
values
('Thingummybob','In Production')

In this really simplistic example you can see we added the OUTPUT clause directly after the insert statement, but before the actual change we wanted to make to the table.  You’ll also notice that we specified that we wanted to look at the inserted rows by prefixing the rest of the statement with the ‘inserted’ keyword.  In this example we followed this with * to return all columns (similar to SELECT *).

With this in mind (because SELECT * is naughty, ok?) we can be more specific about what we want to return:

insert #outputexample
output inserted.WidgetID, inserted.WidgetStatus
values
('Thingummybob','In Production')

And as you could expect, this would give us the WidgetID and WidgetStatus columns that were in the values added to the table.

As you can see, the output clause is pretty much like applying a SELECT statement on the items that have been changed on the table in question. Let’s look at how this might work with delete.  For this example let’s add a number of items to the table so we’ve more to work with:

insert #outputexample
values
('Long Stand','In Production'),
('Tartan Paint','For Sale'),
('Glass Hammer','Discontinued'),
('Left Handed Screwdriver','Discontinued'),
('Elbow Grease','For Sale'),
('Sky Hooks','Discontinued')

I’m now going to delete all ‘Discontinued’ items, and use the OUTPUT clause to return a list of the deletions:

delete from #outputexample
output deleted.*
where WidgetStatus = 'Discontinued'

Again, the OUTPUT clause is positioned directly after the delete statement but before the conditions for the delete itself, and the rest of the clause is prefixed with the ‘delete’ keyword (and another naughty *).  As you can see this is also pretty much like writing a ‘SELECT *’ from the deleted values from the table, and we could/should once again substitute the deleted.* for a comma separated list of column names, or even calculated columns with aliases if we wanted.

Another useful thing about this technique is that you can insert your output into another table if you wish.  Look at the example below:-

if object_id('tempdb..#outputexample') is not null drop table #outputexample;
if object_id('tempdb..#deleteditems') is not null drop table #deleteditems;
go
create table #outputexample
(
       WidgetID int identity(1,1),
       WidgetName varchar(200),
       WidgetStatus varchar(20)
)

create table #deleteditems
(
       WidgetID int,
       WidgetName varchar(200),
       WidgetStatus varchar(20),
       DeletedDateTime datetime
)
insert #outputexample
values
('Long Stand','In Production'),
('Tartan Paint','For Sale'),
('Glass Hammer','Discontinued'),
('Left Handed Screwdriver','Discontinued'),
('Elbow Grease','For Sale'),
('Sky Hooks','Discontinued')
delete from #outputexample
output deleted.*, getdate()
into #deleteditems
where WidgetStatus = 'Discontinued'
select * from #deleteditems

In this case, I’ve created a table to store deleted item details, including a deleted date and time.  Using the output clause, I’ve been able to store the details of the deleted rows, including the date and time the deletion happened.  This is stupidly simple example, but something like this could be used for auditing purposes perhaps, if given more thought.

These were only very basic explanations of how the OUTPUT clause could be put to use, but as you can see it is quite a powerful command with multiple potential uses.  I encourage you to play around with this handy little technique and see what kinds of uses to which you can put it.