declare @a table(a int not null primary key)
insert into @a values (1)
insert into @a values (2)
insert into @a values (10)
insert into @a values (1000)select c from
(select t1.b+t2.b*10+t3.b*100+t4.b*1000 c from
(select 0 as b union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t1,
(select 0 as b union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t2,
(select 0 as b union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t3,
(select 0 as b union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t4
) t5
where c>0 and c not in (select a from @a) order by c
insert into @a values (1)
insert into @a values (2)
insert into @a values (10)
insert into @a values (1000)select c from
(select t1.b+t2.b*10+t3.b*100+t4.b*1000 c from
(select 0 as b union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t1,
(select 0 as b union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t2,
(select 0 as b union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t3,
(select 0 as b union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t4
) t5
where c>0 and c not in (select a from @a) order by c
set @num=(select max(id) from 表)
set @i=1
while @i<=@num
begin
insert #temp values (@i)
set @i=@i+1
endinsert into 表
select a from #temp where a not in (select id from 表)
2、用游标一行一行的比较若不存在某一整数则插入需要的表中(游标所取的要按数列的降序排序,从max(数列)到1一行一行的比较)
insert 你要被插入的表 (列名) select 序号 from (select top 100 (select sum(1) from sysobjects where id<=tem.id) 序号 from sysobjects tem) tem2 where 序号<(select max(你的列) from 你的表) and 序号 not in (select 序号 from 你的表)
2.创建另一个表B存储你的数组,字段BI,这个就经常改吧
3.select A.* into #tmp
from A left join B on A.AI=B.BI
where B.BI is NULL#tmp表里的就是你需要的整数了
set @num=(select max(id) from 表)
set @i=1
while @i<=@num
begin
insert #temp values (@i)
set @i=@i+1
endinsert into 表
select a from #temp where a not in (select id from 表)
declare @i int
declare @s int
select @s = max(a) from table1
set @i = 1
while @i <= @s
begin
if not exists (select 1 from table1 where a = @i)
insert #t select @i
set @i = @i + 1
end
select * from #t
drop table #t
select top 8000 identity(int,1,1) as N into numtab from
(select top 100 id=1 from sysobjects) as a,
(select top 100 id=1 from sysobjects) as b,
(select top 100 id=1 from sysobjects) as c
2,declare @a table(a int not null primary key)
insert into @a values (1)
insert into @a values (2)
insert into @a values (10)
insert into @a values (1000)select a.N from Numtab a left join @a b on a.N=b.a where b.a is null