OUTPUT values

Question

OUTPUT values

Steve Jones - SSC Editor

SSC Guru

Points: 734418
More actions
August 6, 2018 at 10:53 pm

#100981

Comments posted to this topic are about the item OUTPUT values

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Stewart "Arturius" Campbell SSC Guru Points: 72485 More actions · Answer 1

Nice question, thanks Steve
some interesting red herrings...

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”

t.ovod-everett Default port Points: 1487 More actions · Answer 2

One observation is that outputing just the OrderKey column won't enable you to easily determine which of the two inserted records got which of the two OrderKey values. One might assume, but I do not believe it is guaranteed, that the records will insert in the same order they are specified in the VALUES clause. If there is a column in the VALUES clause known to be uniquely identify rows in this insert (i.e. perhaps one knows that the CustomerKey is unique for this specific insert, or perhaps the OrderDate), one could use OUTPUT Inserted.OrderKey, Inserted.CustomerKey (or something similar) to output that data.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

t.ovod-everett - Tuesday, August 7, 2018 1:29 PM
One observation is that outputing just the OrderKey column won't enable you to easily determine which of the two inserted records got which of the two OrderKey values. One might assume, but I do not believe it is guaranteed, that the records will insert in the same order they are specified in the VALUES clause. If there is a column in the VALUES clause known to be uniquely identify rows in this insert (i.e. perhaps one knows that the CustomerKey is unique for this specific insert, or perhaps the OrderDate), one could use OUTPUT Inserted.OrderKey, Inserted.CustomerKey (or something similar) to output that data.

This is really about the principle behind using the OUTPUT clause, not what you would do in a real situation. Understand the principle and you can use it in many different ways.

t.ovod-everett Default port Points: 1487 More actions · Answer 4

Lynn Pettis - Tuesday, August 7, 2018 1:37 PM
This is really about the principle behind using the OUTPUT clause, not what you would do in a real situation. Understand the principle and you can use it in many different ways.

I understand the point of the original question was to illustrate the OUTPUT clause. My point was to reinforce the importance that query writers always assume that records can get re-ordered. If it is important to know which record got which OrderKey, then one needs a column (or combination thereof) that can be used to uniquely identify the data being inserted, or the INSERT should be split into two INSERTs. It is easy for a developer to assume that just because it worked a given way 10 out of 10 times when they tested it that it will work that way always, and that isn't a safe assumption.

This raises one limitation of the OUTPUT clause for INSERTs, namely the inability to output columns that aren't in the destination table. This limitation isn't present when using the MERGE statement. I picked up the MERGE trick from https://stackoverflow.com/questions/10949730/is-it-possible-to-for-sql-output-clause-to-return-a-column-not-being-inserted and http://sqlblog.com/blogs/adam_machanic/archive/2009/08/24/dr-output-or-how-i-learned-to-stop-worrying-and-love-the-merge.aspx. I've used this approach in a situation where a production application got accidentally pointed at a test database. There were a number of records that had to be migrated from test to production, and since it was a well-normalized database with widespread use of IDENTITY columns for Primary Keys, maintaining a good mapping table for which records got which new Primary Key values was critical. The MERGE approach in conjunction with the OUTPUT clause made that doable - the OUTPUT clause could include both the IDENTITY column for the Inserted table and the IDENTITY column from the Source table, and all of that could be written to a "cleanup" table that could then be used for mapping foreign keys in child records.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Okay........
It is just the QotD, it isn't rocket science or anything like that.

Shayn Thomas SSCertifiable Points: 5863 More actions · Answer 6

Good Question Steve,

Thanks

---------------------------------------------------------------------------------------
The more you know, the more you know that you dont know