Remember that SCOPE_IDENTITY returns LAST IDENTITY value for ANY table in CURRENT SESSION.
means that if you have INSERTs of more than one tables, then what will be the SCOPE_IDENTITY value?
Tables creation CREATE TABLE Table1 ( ID int identity( 1,1), Name varchar(100))
GO
CREATE TABLE Table2 ( ID int identity( 10, 1), Name VARCHAR(100))
GO
In another session, try to do the following inserts...
INSERT Table1 VALUES( 'First Insert statemnt in current session-Table1')
INSERT Table2 VALUES( 'LAST Insert statemnt in current session-Table2')
SELECT SCOPE_IDENTITY() ScopeIdentityValueinCurrentSession
Output: ScopeIdentityValueinCurrentSession
10
so it is very clear that the identity value from SCOPE_IDENTITY() varies based on the place where you are using SCOPE_IDENTITY()...
It will return the LAST identity value of JUST previous INSERT statement..
i.e. it is current session scope and also it is irrespective to table
If you insert 2nd record into both tables and using SCOPE_IDENTITY() function to know the each table's IDENTITY value, we can code like below...
INSERT Table1 VALUES( 'First Insert statemnt in current session-Table1')
SELECT SCOPE_IDENTITY() ScopeIdentityValueOfTable1
INSERT Table2 VALUES( 'LAST Insert statemnt in current session-Table2')
SELECT SCOPE_IDENTITY() ScopeIdentityValueOfTable2
Output: ScopeIdentityValueOfTable1
2
ScopeIdentityValueOfTable2
11