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?

insert into DBA.Badges (UserId, Name, Date)
values (21794,'Presentre', GETDATE())
select @@IDENTITY

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:

insert into DBA.Badges (UserId, Name, Date)
OUTPUT inserted.Id
values (21794,'Demoer

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.

delete from DBA.Badges
OUTPUT Deleted.*
where UserId=21794 and Name='Demoer', GETDATE())

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.

update DBA.Badges 
set name='Presenter' 
OUTPUT deleted.*, inserted.*
where UserId=21794 and name='Presentre'

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.