OUTPUT values

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715912

    Comments posted to this topic are about the item OUTPUT values

  • Stewart "Arturius" Campbell

    SSC Guru

    Points: 71401

    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

    Ten Centuries

    Points: 1298

    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: 442144

    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

    Ten Centuries

    Points: 1298

    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: 442144

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

  • Shayn Thomas

    SSCertifiable

    Points: 5411

    Good Question Steve,

    Thanks

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

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

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