当执行set identity_insert tableName on 之后,便可以显示的为表中的自增列插入值,但当插入完成之后,应该尽快的把该属性设为OFF,否则自增列将无法工作。
看代码:
看代码:
1 create table #test
2 (
3 id int identity(1,1),
4 name char(10)
5 )
6 go
7
8 create procedure insertItem
9 @name char(10)
10 as
11 begin
12 declare @id int
13 insert into #test(name) values(@name)
14 set @id=@@identity
15 if(@@error=0)
16 return @id
17 else
18 return @@error
19 end
20
21 declare @id int
22 execute @id=insertItem 'yangkai'
23 print @id
24
25 set identity_insert #test on
26 insert into #test(id,name) values(2,'aa')
27 set identity_insert #test off
28
29 select * from #test
2 (
3 id int identity(1,1),
4 name char(10)
5 )
6 go
7
8 create procedure insertItem
9 @name char(10)
10 as
11 begin
12 declare @id int
13 insert into #test(name) values(@name)
14 set @id=@@identity
15 if(@@error=0)
16 return @id
17 else
18 return @@error
19 end
20
21 declare @id int
22 execute @id=insertItem 'yangkai'
23 print @id
24
25 set identity_insert #test on
26 insert into #test(id,name) values(2,'aa')
27 set identity_insert #test off
28
29 select * from #test