5 Things Series – Output Clause

In this series I’m going to detail the 5 T-SQL Commands that review in my  5 T-SQL Commands I’ve been Missing, but were there the whole time presentation.  The gist of this presentation details my past as an uninformed developer, as it pertains to T-SQL and its vast features.   This presentation, and accompanying blog series, will highlight some of the commands I’ve found useful.

For this demo I have downloaded the DBA.StackExchange.com data dump from this torrent, and loaded it with the Stack Overflow Data Dump Importer that Jeremiah Peschkaj has cloned over to Github.


Have you ever done something like this?  You insert into a table, then select @@IDENTITY, or @SCOPE_IDENTITY  to get back its newly created identity value?

While not celebrated as amazing or revolutionary now, SQL 2005 came with a ton of T-SQL additions and improvements.  One of those improvements was the Output Clause.

Microsoft tells us that the OUTPUT clause:

›Returns information from, or expressions based on, each row affected by an INSERT, UPDATE, or DELETE statement. These results can be returned to the processing application for use in such things as confirmation messages, archiving, and other such application requirements. Alternatively, results can be inserted into a table or table variable.
Basically, we can ‘output’  from the same statement that did the INSERT, UPDATE or delete.  So our prior example can be rewritten as this:

That’s pretty neat, but where did that inserted table come from? That’s a virtual table of the data that was inserted, WITH any auto-generated fields.  If you have an IDENTITY field, such as the Id field in the above example, it will be presented, anything with a default value will also be presented in the inserted table.   For the most part you can treat this table just like any other table as far as selects go.

The wonder of this command does not end with inserts, it also extends to deletes as well.  Since i have ‘Demoer’ sounds dumb, let’s get rid of that.

You can see from the above example that we also get the Deleted table.  This is a great way to validate what was deleted was what you wanted to delete.

For updates, we do not get an updated table, but we do get both the inserted and deleted tables, from which we can combine the output to see our before and after picture.

While I’ve found great use for these in ad-hoc situations, I’ve also utilized the OUTPUT clause to replace the insert, select @@IDENTITY pattern I’ve shown above.  There are some gotchas that are detailed on the MSDN page for this command, but for the most part those are edge cases that you should review if the OUTPUT isn’t as you expect.