Retrieving Identities on set-based inserts

  • Thanks for taking the time to write this article.

    Jason...AKA CirqueDeSQLeil
    I have given a name to my pain...MCM SQL Server, MVP
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Hi weitzera

    Sorry for the late feedback.

    ... and thanks for this lesson. Apparently I did all my test wrong. So there seem to be only two ways to get correct identity values from a bulk insert seem to be:


    A uniqueness within the inserted rows and an OUTPUT clause that returns the id and the other unique value. Sadly OUTPUT currently only works for values that are inserted into the destination table.


    The SS2k8 MERGE statement which allows to return values from source table that are not inserted into destination. But MERGE does not give the best performance for many calls with less data.

    Thanks again


  • Good RBAR article. I have used this before with much success. The code has been in production for a couple of years now. As some have pointed out, just be aware of the pitfalls. As Scott McNitt points out be sure you are aware of any concurency issue.

  • very nice article and follow up discussion!

  • Beautiful and helpful article, thank you:-)

Viewing 5 posts - 16 through 19 (of 19 total)

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