sqlsql-servert-sql

How to get the first available number in a range

What I need

Given a set of numbers from 1 to a limit n, get the smallest one which is not occupied by another record

Details

I was tasked to upload some customer information into a Database; it needs to support legacy features so I don't have control over it's structure.

There is a column "Code" which is INT, UNIQUE and already has a some data inserted into it, but its not continuous, so for example it can have the following records (1,2,3,5,7,125,200,500,127000)

I need to insert every new record with a number that is not already present but I don't want to leaving "holes" in the data.

I cannot simply SELECT the greatest one and add one, because the greatest one might be very close to the limit of the column while there is plenty available numbers in between. And I need to insert thousands of records.

So for example for the records in my example above, this function would return me "4" as "1", "2" and "3" are already used. Then If I insert a record using "4" it would then return "6" cause "5" was already there (so note that I cannot use the last inserted and add one cause the next one might be used as well)... and so on

What I've tried

I actually accomplished it using the following login (I write pseudocode to simplify as the query contains many columns which make the query really long)

-- for each record
DECLARE i = 0
WHILE i < limit
    IF (COUNT(*) FROM MyTable WHERE Code = i) = 0
        INSERT INTO MyTable (Code, ...) VALUES (i, ...)
        BREAK
    SET i = i+1;

This works, but make the query really long and its not very efficient as if I have 1000 insertions and records from 1 to 500 ill be looping 500000 times. Is there a better way to do this?

Something like INSERT INTO MyTable (Code, ...) VALUES (SMALLEST_AVALIABLE(Code), ...)

Thank you so much for your time!

Solution

  • How to do it with a query:

    with range as (
  select min(code) as min, max(code) as max from MyTable
), nums as (
    select min as x from range
    union all
    select x + 1 from nums 
    where x < (select max from range)
)
insert into MyTable (code)
select x from nums
left join MyTable on code = x
where code is null
option (maxrecursion 0)

    See live demo.

    Note: This is not that efficient, except for the coder.