Retrieving the ID of a record just inserted

Question

Retrieving the ID of a record just inserted

martincross

SSC Eights!

Points: 893
More actions
August 26, 2004 at 1:34 pm

#164726

We've been blissfully running these two lines to insert a record, and to retrieve the unique ID of the record just inserted.

INSERT INTO FIFO ( xxx,xxx,xxx,xxx ) VALUES( xxx,xxx,xxx,xxx ) // Insert record
SELECT MAX( UID ) AS Value FROM FIFO // Get the UID of the record just inserted

However, now we've a lot of users running our app, we're possibly getting someone else inserting a record at roughly the same time, and therefore the retrieval of the ID is returning the ID of someone else's record.

I'm guessing we might be able to solve this with some form of transaction, but I'm not clued up on it - anyone have some bright ideas?

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

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

Wayne Lawton Hall of Fame Points: 3532 More actions · Answer 1

Take a look at scope_identity in books online. It may meet your needs.

"SCOPE_IDENTITY Returns the last IDENTITY value inserted into an IDENTITY column in the same scope. A scope is a module -- a stored procedure, trigger, function, or batch. Thus, two statements are in the same scope if they are in the same stored procedure, function, or batch"

Hope this helps

/Wayne

Jean-Pierre PONS SSC-Addicted Points: 497 More actions · Answer 2

if your column is not IDENTITY qualified, you should consider encapsulation of the insertion in a stored proc with an output parameter returning the inserted value :

1° Begin transaction

2° inserting de record

3° getting max(id) into return parameter

4° commit transaction

martincross SSC Eights! Points: 893 More actions · Answer 3

Thanks for that.

The stored procedure will have to perform an EXEC sp_executesql, as I'm passing dynamic INSERT statements. The SP cannot be table specific.

Therefore, it would seem the INSERT is being performed in another scope and scope_identity() returns diddly squat.

I will have to wrap this in a transaction, and hope that solves the problem.

Thanks guys.

Jason Guss SSC Enthusiast Points: 137 More actions · Answer 4

We use RETURN @@Identity as in the example below:

INSERT INTO tblUsers (FName, LName)

VALUES ('John', 'Smith')

RETURN @@Identity

GO

Lynn Guy SSC Journeyman Points: 87 More actions · Answer 5

Watch out for @@identity - if there is a trigger which causes rows to be inserted in another table you will get the id of the LAST row inserted, regardless of the table.

Calvin Lawson SSChampion Points: 11030 More actions · Answer 6

OK, I hesitate to even offer this solution. Nevertheless, it's saved my bacon, and is useful although not optimal as far as performance is concerned:

---TEST Table

if object_ID('tempdb..##Test') is not null drop table ##Test

create table ##Test (ID int identity)

-----------------------------------------------

--declare temporary table to hold returned ID

if object_ID('tempdb..#ID') is not null drop table #ID

create table #ID (ID int)

--Create variable to hold ID

declare @ID int

--Populate temp table

Insert #ID

EXEC sp_executesql N'Insert ##Test Default values

select scope_identity()'

--Populate variable

select @ID = ID

From #ID

---Process Variable

select @ID

--clear temp table

truncate table #ID

Signature is NULL

Alex Kachtanov SSC Veteran Points: 200 More actions · Answer 7

If you can add one more column to your table than:

create table FIFO(xxx,xxx,xxx, GUID uniqueidentifier)

declare @GUID uniqueidentifier

set @GUID = NewID()

INSERT INTO FIFO ( xxx,xxx,xxx,xxx, GUID&nbsp

VALUES( xxx,xxx,xxx,xxx, @GUID&nbsp

// Insert record

SELECT UID AS Value

FROM FIFO

where GUID = @GUID // Get the UID of the record just inserted

It works much faster than using temporary table, especially if you have an index on GUID column.

Hope this help.

Alex