Thank you, Vojislav. Yes, I applied SCOPE_IDENTITY() and it works.
>There are several ways to handle this, and the SCOPE_IDENTITY approach is certainly tried and true.
>
>INSERT INTO MyTable (ColumnList)
>OUTPUT Inserted.*
>VALUES (ValueList)
>
>And the insert statement will give you a snapshot of the row after the insert finishes. This can be very helping in getting the state of the row after the insert, and potentially eliminates the need to make a round trip back to the server to query the new row from the value returned by scope_identity.
>
>
>This is what I used (or tested) last night. I placed
>
>Set @NewPk = SCOPE_IDENTITY()
>
>right after the INSERT where I am looking for an Identity value. And it worked.
>Thank you.
>
>>Use SCOPE_IDENTITY()
>>
>>Alternative way would be to use OUTPUT clause and you can even write a single insert command to insert in both tables at once (composable DML).
>>
>>>Hi,
>>>
>>>I am sure I have done it before but forgot how to do it. In a Stored Procedure, the code Inserts a row into a table.
>>>How do I get the PK (Identity) of the newly added row?
>>>Also, is it after COMMIT or before?
>>>
>>>TIA
>>>
>>>UPDATE. I code I found is as follows (right after INSERT)
>>>Set @NewPk = SCOPE_IDENTITY()
>>>However, the stored procedure does two tables INSERT. If I do the SCOPE_IDENTITY() right after the table where I need the Identity, will it work?
>>>Google shows two more approaches:
>>>@@Identity.
>>>Ident_Current()
>>>But which one to choose?
"The creative process is nothing but a series of crises." Isaac Bashevis Singer
"My experience is that as soon as people are old enough to know better, they don't know anything at all." Oscar Wilde
"If a nation values anything more than freedom, it will lose its freedom; and the irony of it is that if it is comfort or money that it values more, it will lose that too." W.Somerset Maugham