declare @num int,@maxid int set @num =1 select @maxid =max(id) from test create table #t ( id int ) while(@num <= @maxid) begin insert into #t select top 1 @num from test where not exists (select id from test where id = @num) set @num = @num + 1 end select * from #t drop table #t
利用存储过程吧create proc ins @id int as begin tran if exists (select * from table1 where id=@id) begin rollback tran print'该编号已存在' end else begin insert into table1 (id) values(@id) commit tran end 测试数据exec ins 测试编号
set @num =1
select @maxid =max(id) from test
create table #t
(
id int
)
while(@num <= @maxid)
begin
insert into #t select top 1 @num from test where not exists (select id from test where id = @num)
set @num = @num + 1
end
select * from #t
drop table #t
@id int
as
begin tran
if exists (select * from table1 where id=@id)
begin
rollback tran
print'该编号已存在'
end
else
begin
insert into table1 (id) values(@id)
commit tran
end 测试数据exec ins 测试编号