OUTPUT Clause Basics (For Inserts and Deletes)

  • m.richardson.home

    Old Hand

    Points: 301

    Comments posted to this topic are about the item OUTPUT Clause Basics (For Inserts and Deletes)

  • BrainDonor

    SSCoach

    Points: 19206

    OUTPUT has an impact upon @@IDENTITY and SCOPE_IDENTITY() that would be worth knowing about. I learned it the hard way - The Joys of @@ODENTITY, SCOPE_IDENTITY() and IDENT_CURRENT

    Steve Hall
    Linkedin
    Blog Site

  • m.richardson.home

    Old Hand

    Points: 301

    Thanks Steve - wasn't aware it did this..

  • allinadazework

    SSCarpal Tunnel

    Points: 4361

    Very useful article... although you forgot to included "Spirit Level Bubbles" in your list 🙂
    I'd be interested to know when you would use an output table to do this sort of change tracking and when you would use a trigger? I can see for example that it's more transparent to do it in the same procedure that is making the changes, but a trigger would catch all changes not just ones from this procedure. Anyway, be interested to hear why/when it would be better to use output as opposed to triggers?
    Thanks
    Post reply

  • m.richardson.home

    Old Hand

    Points: 301

    Thanks for your feedback - great question too!  I completely agree with you that triggers are a more complete solution, but this is such a handy technique I figured it worth sharing.  I find it really handy when debugging inserts and deletes (so I can actually get a feel for what's happening and quickly). I'm looking at writing a couple more articles around this subject so will likely add some discussion around the advantages / disadvantages.
    Thanks,
    Mat

  • dileep_Vanka

    SSC Rookie

    Points: 39

    there was error in the datatype size, please update it from Varchar(10) to varchar(100)

  • allinadazework

    SSCarpal Tunnel

    Points: 4361

    Of course, I hadn't thought about using it for debugging and checking purposes - was purely thinking about tracking (single track mind I guess)! I tend to write all my inserts/updates/deletes as selects first to ensure I'm getting the right rows and then convert them to deletes/inserts/updates at the end. But I guess using "output" could be more effective for debugging code with multiple steps. Definitely adding this to my tool belt. Thanks again.

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

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