高效地插入1-N的连续数字并重新编号重复项

Question

我有一个表，其主键是正整数：

CREATE TABLE T
(
    ID int PRIMARY KEY CHECK (ID > 0) -- not an IDENTITY column
    -- ... other irrelevant columns...
)

给定一个正整数N，我想插入N个记录，ID为1-N，如果已经存在一个特定的ID记录，则希望插入下一个最高未使用的ID。例如，当N= 5时：

If the table contains...    Then insert...
  (Nothing)                   1,2,3,4,5
  1,2,3                       4,5,6,7,8
  3,6,9,12                    1,2,4,5,7

这是一种比较朴素的方法：

这里有一个天真的做法：

DECLARE @N int = 5 -- number of records to insert
DECLARE @ID int = 1 -- next candidate ID
WHILE @N > 0 -- repeat N times
BEGIN
    WHILE EXISTS(SELECT * FROM T WHERE ID = @ID) -- conflicting record?
        SET @ID = @ID + 1
    INSERT T VALUES (@ID)
    SET @ID = @ID + 1
    SET @N = @N - 1
END

但是如果E是现有记录的数量，那么在最坏的情况下，此代码执行E + N次 SELECT 和 N次 INSERT，效率相当低。

是否有一种聪明的方法可以使用少量SELECT和仅一个INSERT来执行此任务？

- Michael Liu

---

你可以轻易地使用一个左连接的计数表来解决这个问题。http://www.sqlservercentral.com/articles/T-SQL/62867/ 但是这也引出了另一个问题，为什么你关心这些连续数字中是否存在缺失？ - Sean Lange

当查询并行运行时，这种方法就不再有效了。你为什么要这样做？ - Pரதீப்

看起来一个计数表确实可以解决问题。回答你的问题：实际情况要复杂得多，表格包含混合整数和非整数ID，并且记录会由手动和自动过程插入和删除。随着时间的推移，收敛到连续的ID将使数据更容易查看。 - Michael Liu

Answer 1

我想你可以使用一个计数表和NOT IN语句来完成这个任务。

WITH
    E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
    E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
    E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
    cteTally(N) AS 
    (
        SELECT  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
    )
select N into #temp from cteTally



declare @table table (i int)
insert into @table
values
(3),
(6),
(9),
(12)


insert into @table
select top 5 N from #temp where N not in (select i from @table) order by N


select * from @table

drop table #temp

感谢@SeanLange的压力表演和原创展示

- S3S

Answer 2

试试这个；

insert into T
select top 5
    [ID]
from
    (
    select
        [ID]=RANK()over(order by [ID])+5
    from
        T
    union
    select [ID]=1 union
    select [ID]=2 union
    select [ID]=3 union
    select [ID]=4 union
    select [ID]=5
    )IDs
where 
    not exists(select 1 from T data where data.ID=IDs.ID)

不需要临时表，可能更易于阅读和维护（欢迎纠正：））

- user1529235

---

N = 5 只是一个例子。在实际应用中，记录的数量将由变量 @N 指定。您如何重写查询以处理任意数量的记录？ - Michael Liu