How to capture the correct identity value

I have a stored procedure which will do 2 insert statements on 2 different tables. In my 2nd insert statement, I need to know how to capture the exact identity primary key value of the newly inserted record from the first insert statement. I am not sure how to get the correct key value of the new record because there may be more than one user inserting at the same time. Therefore, it is tough to capture the key value that belongs to the user doing his transaction at the time. Please help out. Thanks in advance.

blumonde

Have you tried this?

SELECT SCOPE_IDENTITY()

|||

INSERT INTO Table1 ...

INSERT INTO Table2(Table1ID) VALUES (SCOPE_IDENTITY()) -- Inserts the identity generated by the previous insert to fill the column Table1ID

SELECT Table2ID,Table1ID

FROM Table2

WHERE Table2ID=SCOPE_IDENTITY()

|||

Thanks for your response, gentlemen.

I tried 'Select @.getKEY = @.@.IDENTITY' right after the first insert and it seems to work pretty good so far. I hope I am doing it the right way. However, I am not sure my method is consistant when several users inserting at the same time.

I think it is better to use 'scope_identity' and follow Motley's method above. I think it can handle multi-tasking better than my method.

Motley, what is the Select below for? I have to use it after the second insert?

SELECT Table2ID,Table1ID

FROM Table2

WHERE Table2ID=SCOPE_IDENTITY()

blumonde

|||

Motley:

INSERT INTO Table1 ...

INSERT INTO Table2(Table1ID) VALUES (SCOPE_IDENTITY()) -- Inserts the identity generated by the previous insert to fill the column Table1ID

SELECT Table2ID,Table1ID

FROM Table2

WHERE Table2ID=SCOPE_IDENTITY()

Motley, what is the Select below for? I have to use it after the second insert? Thanks.

SELECT Table2ID,Table1ID

FROM Table2

WHERE Table2ID=SCOPE_IDENTITY()

blumonde

|||The select just returns both identities for you, incase you need them in your program. If you don't need them returned, you don't need to do it.|||

Motley:

The select just returns both identities for you, incase you need them in your program. If you don't need them returned, you don't need to do it.

Thank you.

blumonde