Insert 1-N sequential numbers efficiently and duplicate duplicates
I have a table whose primary key is a positive integer:
CREATE TABLE T
(
ID int PRIMARY KEY CHECK (ID > 0) -- not an IDENTITY column
-- ... other irrelevant columns...
)
Given a positive integer N, I want to insert N records with IDs 1 through N, inclusive. However, if an entry with a specific ID already exists, I want to add the next highest unused ID instead. For example, with 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
Here's a naive way to do it:
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
But if E is the number of existing records, then in the worst case, this code does E + N SELECT and N INSERT, which is very inefficient.
Is there a sane way to accomplish this task with a small number of SELECTs and only one INSERT?
source to share
You can use a table of tables and NOT IN
I suppose ...
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
Credit to @SeanLange For underline tables tables and initially showing me
source to share
Try it;
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)
No temporary tables needed and maybe easier to read and maintain (happy to be fixed :))
source to share