Safely select what I just inserted?

I was thinking that this question probably comes up a lot...but my forum searches didn't turn anything up, so here goes:

I have an id field that auto increments (identity) for each record.
I insert a record.
How can I safely extract the id field of the record for use in another query, avoiding the possiblity that another record is inserted before I can get at it?

Thanks,
G

I forgot to mention I have to do this through coldFusion. For now I'm using

which seems to make sense. Let me know if there's a better way!

Use a stored procedure to do the insert and return the identity value.

Lucas Alexander
http://www.alexanderdevelopment.net

well, using a transaction lock around INSERT and SELECT @@IDENTITY is one method

calling a stored procedure is another method

here's a third: use two queries, one for the INSERT and then another for the SELECT, except in the SELECT what you're doing is querying the row based on the values of the other field(s) which identify the row (technically called a candidate or alternate key)

every table that uses a surrogate key has somewhere lurking within it a column or set of columns that uniquely identifies the row

(if it doesn't, some people will argue that you have a substantial design problem)

so just query the row back using the values of those fields, which will still be sitting there in your CF variables

and the best part: no transaction lock required

That's true. I've done just that in the past, but I always had this fear in the back of my mind...what if? what if somehow another record with the same set just happens to get in there? I want it to be utterly impossible!

In this case you're right though. There's a user id in the row, so it would be damn difficult for a race condition to occur.

I suppose a user could give his login to someone else and they could both click at the same time....!

"another record with the same set" is the crux of the matter

have a look at the table design and find the "real" key

(it isn't the primary key, because if you're using an identity column, chances are you've declared that as the primary key)

this "real" key should have a unique constraint declared on it

if you weren't using an identity column, what would be the primary key?

make sure it has a unique constraint

then the "same set" can never happen

One caveat...

@@IDENTITY returns the last IDENTITY produced on a connection regardless of which table produced the IDENTITY value. So if you have a trigger on a table that creates an IDENTITY value, you would get the IDENTITY of the trigger table, not the primary table.

If you want to be safe and you're using SQL 2000, you might want to consider using SCOPE_IDENTITY() instead of @@IDENTITY. It wil return the IDENTITY of the insert within the scope of the inserts your program submits. Incidental inserts outside of that scope (triggers) are ignored.