create table tb(id int identity , name varchar(10)) insert into tb select 'test1' insert into tb select 'test1' delete tb where id=1set identity_insert tb on insert into tb (id,name) select 1,'test2' set identity_insert tb offselect * from tb/* id name ----------- ---------- 2 test1 1 test2(所影响的行数为 2 行) */
--> 测试数据: [tb] if object_id('[tb]') is not null drop table [tb] create table [tb] (id int identity(1,1),name varchar(50)) insert into [tb] select 'aa' union all select 'bb' delete tbinsert into [tb] select 'cc' union all select 'dd' go create proc sp_wsp @name varchar(100) as declare @id int select @id=min(number) from master..spt_values a,(select id=max(id) from tb)b,tb where type='p' and a.number>0 and a.number<=b.id and not exists(select 1 from tb where id=a.number) if(isnull(@id,0)!=0) begin set identity_insert tb on insert into tb(id,name) values(@id,@name) set identity_insert tb off end else insert into tb values(@name) go--插入测试 exec sp_wsp 'ee' --查看 select * from tb--生成id=1--再插入测试 exec sp_wsp 'ee' --查看 select * from tb --生成id=2--再插入测试 exec sp_wsp 'ee' --查看 select * from tb --没有空缺,继续自增。--生成id=5
上面存储过程中,多了个tb,是不需要的。。红色部分可以去掉create proc sp_wsp @name varchar(100) as declare @id int select @id=min(number) from master..spt_values a,(select id=max(id) from tb)b,tb where type='p' and a.number>0 and a.number<=b.id and not exists(select 1 from tb where id=a.number) if(isnull(@id,0)!=0) begin set identity_insert tb on insert into tb(id,name) values(@id,@name) set identity_insert tb off end else insert into tb values(@name) go
insert into tb select 'test1'
insert into tb select 'test1'
delete tb where id=1set identity_insert tb on
insert into tb (id,name) select 1,'test2'
set identity_insert tb offselect * from tb/*
id name
----------- ----------
2 test1
1 test2(所影响的行数为 2 行)
*/
if object_id('[tb]') is not null drop table [tb]
create table [tb] (id int identity(1,1),name varchar(50))
insert into [tb] select 'aa'
union all select 'bb' delete tbinsert into [tb]
select 'cc' union all
select 'dd'
go
create proc sp_wsp
@name varchar(100)
as
declare @id int
select @id=min(number) from master..spt_values a,(select id=max(id) from tb)b,tb
where type='p' and a.number>0 and a.number<=b.id
and not exists(select 1 from tb where id=a.number)
if(isnull(@id,0)!=0)
begin
set identity_insert tb on
insert into tb(id,name) values(@id,@name)
set identity_insert tb off
end
else
insert into tb values(@name)
go--插入测试
exec sp_wsp 'ee'
--查看
select * from tb--生成id=1--再插入测试
exec sp_wsp 'ee'
--查看
select * from tb --生成id=2--再插入测试
exec sp_wsp 'ee'
--查看
select * from tb --没有空缺,继续自增。--生成id=5
@name varchar(100)
as
declare @id int
select @id=min(number) from master..spt_values a,(select id=max(id) from tb)b,tb
where type='p' and a.number>0 and a.number<=b.id
and not exists(select 1 from tb where id=a.number)
if(isnull(@id,0)!=0)
begin
set identity_insert tb on
insert into tb(id,name) values(@id,@name)
set identity_insert tb off
end
else
insert into tb values(@name)
go