When you NEED to Declare a Table Using the OUTPUT Clause

I learned a thing myself at the end!

Words: 532

Time to read: ~ 2.5 minutes

Update: 20180417 – Added explanation of SQL Script rather than random statements.

Here’s a question for you.

  • Would you create a temporary table just so that you could SELECT out the data that you have INSERTed into it?

I’m not trying to catch you out here, I’m talking a straightforward INSERT INTO #temp (col1, col2...) SELECT col1, col2followed by a SELECT col1, col2 ... FROM #temp?

Seems like a completely extra, unnecessary step, right?

  • Would you do the same thing with a declared table variable i.e. DECLARE @temp TABLE (col1, col2, ...)?

It’s the same problem, no? You store the results temporarily just to return them again, unchanged and unaltered.

What about with the OUTPUT clause?

Take OUTPUT for example. This clause is perfect if we want some visibility on any Data Manipulation Language (DML) that we run in SQL Server.

Need to see what you’ve deleted? Try using OUTPUT as part of your DELETE query.

DELETE FROM dbo.CustomersTableIdent
OUTPUT deleted.id, deleted.FirstName, deleted.LastName
WHERE id % 5 = 0

Here we’re only returning the inserted rows where the inserted id is divisible by 5.

What have I done?

Need to see what you’ve inserted? Try using OUTPUT as part of your INSERT query.

INSERT INTO dbo.CustomersTableIdent (FirstName, LastName)
OUTPUT inserted.id, inserted.FirstName, inserted.LastName
VALUES ('My first name', 'My last name');

I’m not great with surveys…

Need to see what you’ve updated? …

I think you get the picture.

Is there a case where we HAVE to?

There is this got’cha with declared table variables where they can store data that was in a transaction that has been rolled back.

Because table variables have limited scope and are not part of the persistent database, they are not affected by transaction rollbacks

DECLARE @MyHoldingTable TABLE (id int NOT NULL, FirstName varchar(50) NOT NULL, LastName varchar(50) NOT NULL);
INSERT INTO dbo.CustomersTableIdent (FirstName, LastName)
OUTPUT inserted.id, inserted.FirstName, inserted.LastName
INTO @MyHoldingTable (id, FirstName, LastName)
VALUES ('My first name (removed)', 'My last name (removed)');
SELECT id, FirstName, LastName FROM @MyHoldingTable;

I should be removed but I’m not…

That’s not the point I’m trying to get across here. We don’t want to use the results for later purposes, we want to show them immediately; a quick check to see that what we’ve inserted is what we expected.

Until the last day, I couldn’t think of a reason why you’d need to do this and thought that you could happily continue along OUTPUT-ing results directly. Luckily (or unluckily), experience is the best teacher.

There is a time when you will NEED to input into a declared table first

We run an INSERT statement using our OUTPUT clause to see the results and, violà!, we have an error message:

The target table ‘dbo.WishList’ of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause.

Checking the table in question, we indeed have a trigger:


It’s not very helpful but it’s legacy code that we can’t change…

ON dbo.WishList
RAISERROR(N'Looks like you''ve inserted some records', 16, 10);

Why? Why would someone do this???!!!

So there is our reason! When there is a trigger on the table in question, we cannot just output the results from our OUTPUT clause. We need to use the INTO statement as well.

DECLARE @MyWishList TABLE (id int, Wish varchar(20));
INSERT INTO dbo.WishList (id, Wish)
OUTPUT inserted.id, inserted.Wish
INTO @MyWishList (id, Wish)
VALUES (1, 'I')
, (2, 'Want')
, (3, 'To')
, (4, 'Go')
, (5, 'To')
, (6, 'PSSummit')
, (7, 'Next year!')
SELECT * FROM @MyWishList;

Note: Funnily enough, in SQL Operations Studio this will insert the records but will not show the results of my SELECT * FROM @MyWishList

That’s a win-win

I get the answer to a problem that I was having and potentially a chance to improve SQL Operations Studio (if this does turn out to be a bug).

I think I deserve a cup of coffee for that…

Author: Shane O'Neill

DBA, T-SQL and PowerShell admirer, Food, Coffee, Whiskey (not necessarily in that order)...

5 thoughts on “When you NEED to Declare a Table Using the OUTPUT Clause”

  1. Very nice! I’ve been writing SQL for 30 years and only just realised thanks to your blog – linked from the SQL Server Central Database Weekly digest – that the OUTPUT clause existed! Which highlights for me the importance of serendipitous learning and curiosity…

    1. Completely agree with you there! I’m hoping to always have the urge to learn and a desire to know.
      Glad you have OUTPUT in your toolkit now, I’m sure you’ll see so many examples where you can use it!

  2. Suggestion: When you post an example, try not to throw in some unexplained and needless extra SQL – as in the first example where you use “WHERE id % 5 = 0”; the focus is supposed to be how to use output, and I spent 10 minutes figuring out that WHERE syntax just to discover the output is filtered to IDs divisible evenly by 5. Not only hard to google and find an answer to, probably an example that no one would ever used, nor is the filter explained with the example.

    1. That’s a fair point Schmith and I’ll take that on board from now on.
      I’ll aim to explain all the SQL that I use in examples going forward. 🙂
      Glad you got to learn something about the modulus operator, I’ve found it handy with a few date and time calculations.

    2. “Here we’re only returning the inserted rows where the inserted id is divisible by 5.”…

Leave a Reply