Get current IDENTITY value : IDENTITY « Sequence « SQL Server / T-SQL

1. SQL Server / T-SQL
2. Sequence
3. IDENTITY

Get current IDENTITY value

17>
18> -- Save the original @@IDENTITY value
19>
20> CREATE TABLE TestIdent
21> (
22>    IDCol int IDENTITY PRIMARY KEY
23> )
24> GO
Msg 2714, Level 16, State 6, Server JAVA2S\SQLEXPRESS, Line 20
There is already an object named 'TestIdent' in the database.
1> CREATE TABLE TestChild1 (
2>    IDcol int PRIMARY KEY FOREIGN KEY REFERENCES TestIdent(IDCol)
3> )
4> GO
1> CREATE TABLE TestChild2 (
2>    IDcol int PRIMARY KEY FOREIGN KEY REFERENCES TestIdent(IDCol)
3> )
4> GO
1> DECLARE @Ident int
2>
3> INSERT INTO TestIdent DEFAULT VALUES
4>
5> SET @Ident = @@IDENTITY
6> PRINT 'The value we got originally from @@IDENTITY was ' + CONVERT(varchar(2),@Ident)
7> PRINT 'The value currently in @@IDENTITY is ' + CONVERT(varchar(2),@@IDENTITY)
8>
9> INSERT INTO TestChild1 VALUES (@@IDENTITY)
10>
11> PRINT 'The value we got originally from @@IDENTITY was ' + CONVERT(varchar(2),@Ident)
12>
13> IF (SELECT @@IDENTITY) IS NULL
14>    PRINT 'The value currently in @@IDENTITY is NULL'
15> ELSE
16>    PRINT 'The value currently in @@IDENTITY is ' + CONVERT(varchar(2),@@IDENTITY)
17>
18> PRINT ''
19>
20> INSERT INTO TestChild2 VALUES (@Ident)
21> GO

(1 rows affected)

(1 rows affected)
The value we got originally from @@IDENTITY was 2

(1 rows affected)
The value currently in @@IDENTITY is 2

(1 rows affected)

(1 rows affected)
The value we got originally from @@IDENTITY was 2

(1 rows affected)
The value currently in @@IDENTITY is NULL

(1 rows affected)


(1 rows affected)
1>
2> select * from testchild1
3> go
IDcol
-----------
          2

(1 rows affected)
1>
2> select * from testchild2
3> GO
IDcol
-----------
          2

(1 rows affected)
1>
2> drop table TestChild2;
3> drop table TestChild1;
4> GO
1>
2>
           
       

Related examples in the same category