Grabbing IDENTITY on INSERT

Last Post 18 Jan 2011 11:37 PM by ma77g. 2 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
ma77g
New Member
New Member

--
18 Jan 2011 06:19 AM
I need to insert rows in a table and grab the IDENTITY integer they are given to return it for further processing by the calling procedure.

In the past I've used Ident_Current(tCustomer) to get this but on this application its possible that there will quite high concurrency of inserts from different users and I'm wondering if its better to use:

a) a trigger
b) a Table variable with OUTPUT inserted.CustomerID as part of the insert

The problem I can see with 'b' is that the variable name will always be the same in memory regardless of how many insert stored procedures have been called concurrently so the wrong value may get returned to the calling procedure.

Have I missed some important understanding of how this will work?

thanks, Matt
gunneyk
New Member
New Member

--
18 Jan 2011 06:50 AM
Matt,

You definately don't want to use IDENT_CURRENT() for that. You want to use SCOPE_IDENTITY() instead. SCOPE_IDENTITY() will give you the last IDENTITY value for any table in the current session and current scope and other users inserts will not affect that value for each individual session. Here are the defintions from BOL:

IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope.

@@IDENTITY returns the last identity value generated for any table in the current session, across all scopes.

SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.

I don't recommend @@IDENTITY at all anymore unless you needed to get the IDENTITY value of a trigger that inserted into a table with an IDENTITY column. If you are doing single row inserts then SCOPE_IDENTITY is what you want. If you are doing multiple row inserts then using the OUTPUT might be better so you can get all at once.
ma77g
New Member
New Member

--
18 Jan 2011 11:37 PM
Many thanks - thats excellent advice.
You are not authorized to post a reply.

Acceptable Use Policy