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.
Here we’re only returning the inserted rows where the inserted id is divisible by 5.
Need to see what you’ve inserted? Try using OUTPUT as part of your INSERT query.
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
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…
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.
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…