SCOPE_IDENITY() - Incorrect Value (Returned)

There are two tables

Table_1:

ProductID (Identity Increment)

ProductDescription (nvarchar)

Table_2:

ProductID (int)

I have a sql statement and it's like this. This SQL is probably incorrect - but hopefully gives you an idea of what I am trying to do:

DECLARE@.getTheNewProductIDint
INSERT INTOTable_1 (ProductDescription)VALUES('SomeValue') ;SET@.getTheNewProductID =SELECT SCOPE_IDENTITY()
INSERT INTOTable_2 (ProductID)VALUES(@.getTheNewProductID)
  

What I need is: when inserting into Table_1 to get the Exact New Product Id from it that occurs from the identity increment - then insert that exact same product ID into table_2

The problem is it is returning incorrect values on the scope identity. Such as values from two transaction ago.

How do you do this?

I did try using @.@.identity which may have stuffed things up??

Thanks for your help

Hi

Try with this code.

DECLARE@.getTheNewProductIDint

INSERT INTOTable_1 (ProductDescription)VALUES('SomeValue') ;

SET@.getTheNewProductID = (SELECTProductID from Table_1 where ProductID= SCOPE_IDENTITY())

INSERT INTOTable_2 (ProductID)VALUES(@.getTheNewProductID)