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.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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.
Need to see what you’ve inserted? Try using OUTPUT as part of your INSERT query.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| INSERT INTO dbo.CustomersTableIdent (FirstName, LastName) | |
| OUTPUT inserted.id, inserted.FirstName, inserted.LastName | |
| VALUES ('My first name', 'My last name'); |
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
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| BEGIN TRAN; | |
| 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)'); | |
| ROLLBACK | |
| SELECT id, FirstName, LastName FROM @MyHoldingTable; |
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…
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| CREATE TRIGGER Clippy | |
| ON dbo.WishList | |
| AFTER INSERT | |
| AS | |
| RAISERROR(N'Looks like you''ve inserted some records', 16, 10); | |
| GO |
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.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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…
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…
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!
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.
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.
“Here we’re only returning the inserted rows where the inserted id is divisible by 5.”…