finding the id value of an inserted row.

[MMIMadness]

Apologies if this has already been asked and answered couldn't spot anything obvious on the forum.

I have a table with a Primary key that is an identity field and when i enter a new row of data is there an easy way of finding out what the new row's identity is, as i need it for adding another row in another table that is linked via the primary key.

Any ideas would be welcomed..

Thanks in advance.

MmiMadness

 

[amrita418]

scope_identity() should get your the value you inserted.

Regards,
AA

 

[travisbrown]

Like AA said, use Scope_Identity(). You have to be careful with @@identity, which is sometimes used, because you can inadvertently pick up the id of rows inserted into child tables, etc. Not usually an issue with simple insertions into single tables, but best practice and all... Scope_Identity only returns the id in the scope of your current query.

Run your insert, then

declare @ident int
SET @ident = Scope_Identity()

SELECT @ident <-- your new id. Go nuts.

 

[MMIMadness]

if i had two people doing an insert each at the same time will get confused at all and return either the same scope id or the wrong one, or is it tied to the record that was inserted.

 

[vongrunt]

Two people, each has it's own scope.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[travisbrown]

You will be safe with scope_identity(). Only returns the id in the scope of the current query. Will be unaffected but other users.

 

[travisbrown]

Umm.. SELECT 'Will be unaffected but other users' AS 'Will be unaffected by other users'

 

[MMIMadness]

I knew what you ment. :>

thanks for your help, have just put it into a test script and it works a treat. now to buld the full script in a stored procedure.

many thanks guys.

MMIMadness