The OUTPUT Command

  • josephgabrie

    SSC Enthusiast

    Points: 115

    Very useful hint

    Regards

  • DoktorBlue

    SSC Enthusiast

    Points: 107

    Beside some missing spaces in the code, which is confusing, I miss information how the Output option returns its values to the devolper.


    Kindest Regards,

    DoktorBlue

  • Hans van Dam

    SSC-Addicted

    Points: 436

    Very valuable information to me. Too bad that te conclusion is wrong! There really is a need to perform modifications wherever @@identity or scope_identity() is used because they are not reliable:

    • after a rollback, the assigned identity is returned while it does not exist
    • when some other identity is assigned (in any table) between your insert and reading out @@identity you get that other identity

    So, my conclusion would be that it really is nescessary to rewrite code whenever you need the newly assigned identity.

    Regards,

    Hans van Dam

  • Dinesh Asanka

    SSChampion

    Points: 11058

    Comments posted to this topic are about the item The OUTPUT Command




    My Blog: http://dineshasanka.spaces.live.com/

  • Johan van Tonder

    Old Hand

    Points: 371

    Yes indeed. Very valuable feature. Performance can be greatly enhanced by this, where triggers may be replaced. Those wondering about the missing spaces...

    In the first example block, SELECTSCOPE_IDENTITY() should be SELECT SCOPE_IDENTITY(). In the last example block where the delete statements are WHEREID = should be WHERE ID =

     

  • TJ-205129

    Valued Member

    Points: 62

    Useful tip, but I think the editor should have done more editing (and proofreading.)

  • Jimi Meyer

    SSCommitted

    Points: 1764

    Missing some key information such as when you cannot use it. Will not work if there is a trigger on the table, or if it is involved in foreign key, or has check constraints. All of those are pretty common.

  • Dinesh Asanka

    SSChampion

    Points: 11058

    sorry for the query errors




    My Blog: http://dineshasanka.spaces.live.com/

  • Andy Warren

    SSC Guru

    Points: 119694

    Jimi, that's not quite correct. Here's what it says in BOL:

    output_table cannot:

    • Have enabled triggers defined on it.
    • Participate on either side of a foreign key constraint.
    • Have CHECK constraints or enabled rules.

    You can use it on any table regardless of configuration, it's only the output table where you're landing the results that has restrictions.

    The output clause is one of the really nice improvements in 2005. I've seen all too many instances where developers were essentially inserting an alternate primary key so that they could insert multiple rows at once and still get the keys back. If they had used scope_identity() they would have been forced to insert the records one at a time which is not great for performance.

    Essentially the output clause exposes the logical inserted and deleted tables that were previously only visible within the trigger execution.

  • Dinesh Asanka

    SSChampion

    Points: 11058

    Andy,

    is it possible you to do following changes in the article

     

    SELECTSCOPE_IDENTITY() should be SELECT SCOPE_IDENTITY() in first example block.

    In the last example block where the delete statements are WHEREID = should be WHERE ID




    My Blog: http://dineshasanka.spaces.live.com/

  • Mike Dougherty-384281

    SSCrazy

    Points: 2764

    "Select Scope_Identity() as LastInsertID"

    Of course the real-world use of scope_identity() is to set a variable or an output parameter, since only a hack DBA would ever attempt to copy/paste identity values in an interactive session - right?

  • Andy Warren

    SSC Guru

    Points: 119694

    Dinesh, I no longer have the power! But I'll forward the link to Steve for you and he will make the correction, but he's headed to the UK this week so may take a day or two.

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 720491

  • Olga B

    Default port

    Points: 1499

    I wondered the same thing and came up with the following:

    DECLARE @Output table( ID int, Code VARCHAR(25), Name VARCHAR(50), Salary Numeric(10 , 2))

    INSERT INTO TempTable ( Code , Name, Salary)

    OUTPUT Inserted.ID

    , Inserted.Code

    , Inserted.Name

    , Inserted.Salary

    INTO @Output

    VALUES( 'A005' , 'Jennifer', 500 )

    It would have been helpful if OUTPUT INTO were discussed in the article. Otherwise, a great tip!

  • dileep kumar-372369

    SSC Enthusiast

    Points: 191

     Its wonderful !

Viewing 15 posts - 1 through 15 (of 26 total)

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