Getting a serial number after an INSERT

Question

Post reply

Getting a serial number after an INSERT

lindss01

SSCrazy

Points: 2264
More actions
August 16, 2006 at 7:22 am

#94524

I am creating a Stored Procedure to Insert a row in a table. The table has an Identity field in it. Is there an easy method to get the value in that Identity?

Thanks
Steve

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

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

SQLZ SSChampion Points: 12872 More actions · Answer 1

Hi Steve,

Do you mean that you want to get the value of the identity that has just been created by the insert?

To do this you can use scope_identity(), like so:

--to pass the value of the identity into a variable.... declare @new_value int select @new_value = scope_identity()
Hope that helps,

Karl
source control for SQL Server

Denis Grable Newbie Points: 9 More actions · Answer 2

The @@IDENTITY may be even easier. Here is the text from the help:

@@IDENTITY

Return Types

numeric

Remarks

After an INSERT, SELECT INTO, or bulk copy statement completes, @@IDENTITY contains the last identity value generated by the statement. If the statement did not affect any tables with identity columns, @@IDENTITY returns NULL. If multiple rows are inserted, generating multiple identity values, @@IDENTITY returns the last identity value generated. If the statement fires one or more triggers that perform inserts that generate identity values, calling @@IDENTITY immediately after the statement returns the last identity value generated by the triggers. The @@IDENTITY value does not revert to a previous setting if the INSERT or SELECT INTO statement or bulk copy fails, or if the transaction is rolled back.

@@IDENTITY, SCOPE_IDENTITY, and IDENT_CURRENT are similar functions in that they return the last value inserted into the IDENTITY column of a table.

@@IDENTITY and SCOPE_IDENTITY will return the last identity value generated in any table in the current session. However, SCOPE_IDENTITY returns the value only within the current scope; @@IDENTITY is not limited to a specific scope.

IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the identity value generated for a specific table in any session and any scope. For more information, see IDENT_CURRENT.

Examples

This example inserts a row into a table with an identity column and uses @@IDENTITY to display the identity value used in the new row.

INSERT INTO jobs (job_desc,min_lvl,max_lvl)

VALUES ('Accountant',12,125)

SELECT @@IDENTITY AS 'Identity'

SQLZ SSChampion Points: 12872 More actions · Answer 3

Indeed @@identity is another option.

However, because @@identity is not limited by scope I always recommend using scope_identity() instead. Otherwise you could end up with potentially the wrong identity value. Of course, if you're aware of the distinction and your specifically need to the last identity (regardless of scope) then use @@identity.

Karl
source control for SQL Server

Nass Grasshopper Points: 18 More actions · Answer 4

I normally use two id colomns, guid and int

with select NewId() i place a new guid in a varaible and use that one on the insert statement.

than to get the int i use: select int from table where guid = varaible

Only this way i'am sure that sql will give the right int back!

Marc Poulin Grasshopper Points: 13 More actions · Answer 5

SQL Server 2005 also has the IDENT_CURRENT('table-name') function.

Jeff Moden SSC Guru Points: 1004250 More actions · Answer 6

Be careful... IDENT_CURRENT is not scope sensitive and, if someone else did an insert, may not reflect the row that your proc inserted.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)