Using the SCOPE_IDENTITY() Function : SCOPE_IDENTITY « Sequence Indentity « SQL Server / T-SQL Tutorial






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>








15.7.SCOPE_IDENTITY
15.7.1.Last identity value generated for any table in the current session, for the current scope
15.7.2.Using the SCOPE_IDENTITY() Function
15.7.3.SELECT SCOPE_IDENTITY()