SCOPE_IDENTITY() returns the last IDENTITY value inserted by your session into any table that has an IDENTITY column, but only in the current scope.
A scope is a batch, a stored procedure, a trigger, or a function.
7>
8> CREATE TABLE MyTable (
9> key_col int NOT NULL IDENTITY (1,1),
10> abc char(1) NOT NULL
11> )
12> INSERT INTO MyTable VALUES ('a')
13> INSERT INTO MyTable VALUES ('b')
14> INSERT INTO MyTable VALUES ('c')
15> SELECT * FROM MyTable ORDER BY key_col
16>
17>
18>
19> DECLARE @mylastident AS int
20> SET @mylastident = SCOPE_IDENTITY ()
21> PRINT @mylastident
22>
23> drop table MyTable
24> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
key_col abc
----------- ---
1 a
2 b
3 c
(3 rows affected)
3
1>