Discover what the last IDENTITY value:you can use the IDENT_CURRENT function. : IDENT_CURRENT « Sequence Indentity « SQL Server / T-SQL Tutorial

1. SQL Server / T-SQL Tutorial
2. Sequence Indentity
3. IDENT_CURRENT

4>
5> CREATE TABLE MyTable (MyID Int IDENTITY(1, 10) NOT NULL
6>            , MyDescription nVarChar(50) NOT NULL)
7>
8> INSERT MyTable (MyDescription) VALUES ('Auto Record 1')
9> INSERT MyTable (MyDescription) VALUES ('Auto Record 2')
10>
11> SET IDENTITY_INSERT MyTable ON
12>
13> INSERT MyTable (MyID, MyDescription) VALUES (5, 'Manual Record 1')
14>
15> SET IDENTITY_INSERT MyTable OFF
16>
17> INSERT MyTable (MyDescription) VALUES ('Auto Record 3')
18>
19> SELECT * FROM MyTable
20> GO

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)
MyID        MyDescription
----------- --------------------------------------------------
          1 Auto Record 1
         11 Auto Record 2
          5 Manual Record 1
         21 Auto Record 3

(4 rows affected)
1>
2> SELECT @@IDENTITY AS LastIdentity
3> GO
LastIdentity
----------------------------------------
                                      21

(1 rows affected)
1> SELECT IDENT_CURRENT(MyID) AS CurrentIdentity
2> GO
Msg 207, Level 16, State 1, Server J\SQLEXPRESS, Line 1
Invalid column name 'MyID'.
1>
2>
3> drop table MyTable;
4> GO
1>
2>
3>
4>