DECLARE @maxid int,@sql nvarchar(4000)
SELECT id INTO #t
FROM t
IF exists(SELECT id FROM #t)
BEGIN
SELECT @maxid=MAX(id) FROM #t
SET @sql='SELECT TOP '+CAST(@maxid AS varchar)
+' IDENTITY(int,1,1) id INTO ##tem FROM sysobjects a,sysobjects b,'
+'sysobjects c,sysobjects d'
EXEC sp_executesql @sql
IF exists(SELECT id FROM ##tem WHERE id not in (SELECT id FROM #t))
SELECT @maxid=min(id) FROM ##tem WHERE id not in (SELECT id FROM #t)
ELSE
SELECT @maxid=@maxid+1
DROP TABLE ##tem
END
ELSE
SELECT @maxid=1
DROP TABLE #t
print @maxid
SELECT id INTO #t
FROM t
IF exists(SELECT id FROM #t)
BEGIN
SELECT @maxid=MAX(id) FROM #t
SET @sql='SELECT TOP '+CAST(@maxid AS varchar)
+' IDENTITY(int,1,1) id INTO ##tem FROM sysobjects a,sysobjects b,'
+'sysobjects c,sysobjects d'
EXEC sp_executesql @sql
IF exists(SELECT id FROM ##tem WHERE id not in (SELECT id FROM #t))
SELECT @maxid=min(id) FROM ##tem WHERE id not in (SELECT id FROM #t)
ELSE
SELECT @maxid=@maxid+1
DROP TABLE ##tem
END
ELSE
SELECT @maxid=1
DROP TABLE #t
print @maxid
set @i=1
while exist(select ID from 表 where ID=@i)
begin
@i=@i+1
end
insert into 表 (ID) values (@i)
declare @i int
set @i=1
while exists(select ID from 表 where ID=@i)
begin
set @i=@i+1
end
insert into 表 (ID) values (@i)经测试满足要求
select min(new_id) from #temp_table where (new_id not in(select id from #temp_table))上面第二条语句就是取出最小的断号id
insert #t select 1 union select 2 union select 5 union select 6 union select 8select top 1 a.id+1
from #t a left join (select id-1 as id from #t) b on a.id=b.id
where b.id is null
order by a.iddrop table #t
将 原表a 与 id减去1的结果集b 外联接,第一处出现b.id为null的地方就是出现断号的地方。
select top 1 a.id+1
into tablename from #t a left join (select id-1 as id from #t) b on a.id=b.id
where b.id is null
order by a.id