Retrieving Identities on set-based inserts

Question

Retrieving Identities on set-based inserts

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

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Thanks for taking the time to write this article.

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

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 2

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:

1.)

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.

2.)

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

Flo

Jon Russell Hall of Fame Points: 3048 More actions · Answer 3

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.