Using IDENTITY value
20>
21> -- Using @@IDENTITY
22> CREATE TABLE TestIdent(
23> IDCol int IDENTITY PRIMARY KEY
24> )
25> GO
Msg 2714, Level 16, State 6, Server JAVA2S\SQLEXPRESS, Line 22
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>
5> DECLARE @Ident int
6> INSERT INTO TestIdent DEFAULT VALUES
7> SET @Ident = @@IDENTITY
8>
9> PRINT 'The value we got originally from @@IDENTITY was ' + CONVERT(varchar(2),@Ident)
10> PRINT 'The value currently in @@IDENTITY is ' + CONVERT(varchar(2),@@IDENTITY)
11>
12> INSERT INTO TestChild1 VALUES (@@IDENTITY)
13>
14> PRINT 'The value we got originally from @@IDENTITY was ' + CONVERT(varchar(2),@Ident)
15>
16> IF (SELECT @@IDENTITY) IS NULL
17> PRINT 'The value currently in @@IDENTITY is NULL'
18> ELSE
19> PRINT 'The value currently in @@IDENTITY is ' + CONVERT(varchar(2),@@IDENTITY)
20>
21> -- The next line is just a spacer for our print out
22> PRINT ''
23>
24> INSERT INTO TestChild2
25> VALUES (@@IDENTITY)
26> GO
(1 rows affected)
(1 rows affected)
The value we got originally from @@IDENTITY was 3
(1 rows affected)
The value currently in @@IDENTITY is 3
(1 rows affected)
(1 rows affected)
The value we got originally from @@IDENTITY was 3
(1 rows affected)
The value currently in @@IDENTITY is NULL
(1 rows affected)
Msg 515, Level 16, State 2, Server JAVA2S\SQLEXPRESS, Line 24
Cannot insert the value NULL into column 'IDcol', table 'master.dbo.TestChild2'; column does not allow nulls. INSERT fails.
The statement has been terminated.
1> select * from testchild1
2> go
IDcol
-----------
3
(1 rows affected)
1>
2> select * from testchild2
3> GO
IDcol
-----------
(0 rows affected)
1>
2> drop table testchild1
3> drop table testchild2
4> GO
1>
Related examples in the same category