I have long used SCOPE_IDENTITY() to get the primary key of a newly inserted row immediately after the INSERT statement. For example:
INSERT INTO myTable
SELET @var = SCOPE_IDENTITY()
What I incorrectly assumed was that the value was reset with each statement similar to the behavior of @@ERROR. If not each statement, then each INSERT statement. In other words, [@more@]if I insert zero records then I expected SCOPE_IDENTITY to return a null value. In actuallity, both SCOPE_IDENTITY and @@IDENTITY retain thier values until another identity value is generated.
As SQL Server Books Online says:
SCOPE_IDENTITY and @@IDENTITY return the last identity values that are generated in any table in the current session. However, SCOPE_IDENTITY returns values inserted only within the current scope; @@IDENTITY is not limited to a specific scope. weblink
Run the following code against a server to see. (Should work against any 2000 or 2005 server.)
— ============================================
— TEST
— select identities
SELECT 'Before first insert:', @@IDENTITY '@@IDENTITY', SCOPE_IDENTITY()'SCOPE_IDENTITY()',@@ROWCOUNT '@@ROWCOUNT'
— Insert 10 records into table
INSERT INTO @tblTemp01
SELECT TOP 10 SPECIFIC_NAME
FROM INFORMATION_SCHEMA.ROUTINES
— select identities
SELECT 'After first insert:', @@IDENTITY '@@IDENTITY', SCOPE_IDENTITY()'SCOPE_IDENTITY()',@@ROWCOUNT '@@ROWCOUNT'
— Insert 0 records into table
INSERT INTO @tblTemp01
SELECT TOP 10 SPECIFIC_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE SPECIFIC_NAME = 'A_very_unlikely_name_to_RETURn_0_rows'
— select identities
SELECT 'After second insert:', @@IDENTITY '@@IDENTITY', SCOPE_IDENTITY()'SCOPE_IDENTITY()',@@ROWCOUNT '@@ROWCOUNT'
— select from table
SELECT * FROM @tblTemp01
aldkjfkljasd;lkfj a;ldkjf a;lkdsfj
