SQL: Keep finding the next missing value from a column of sequential data without inserting the previous/current missing value

You have a FRUIT table in your database. The data of the table look like below.

fruitKey      name                description
----------      -----------------  -------------------------------------
1                 Apple               Sells best in summer
5                 FruitA              Need to be frozen
6                 FruitB              Great
10               FruitC               Sweet
11               FruitD               For children
12               FruitF                Blue color
19               FruitG                Need to buy more

Now, you have more fruits coming in and you want to put them in your FRUIT table. You want the new records to fill in the fruitKey gaps instead of incrementing the max value of it. The following SQL will get the minimum missing fruitKey which is 2.

SELECT MIN(f1.fruitKey + 1) AS fruitKey
FROM FRUIT f1
Where NOT EXISTS (SELECT * FROM FRUIT f2
                                 WHERE f2.fruitKey = f1.fuitKey + 1;

However, you are required to summit all the insert statements at once for the system to process. The above SQL will not return the next missing fruitKey which is 3, instead, it will always return 2. The SQL below will return all the gaps in the fruitKey.

SELECT (f1.fruitKey + 1) AS fruitKey_low,
       (select min(f1.fruitKey) - 1 from FRUIT f3
            where f3.fruitKey > f1.fruitKey) AS fruitKey_high
FROM FRUIT f1
WHERE NOT EXISTS (SELECT * FROM FRUIT f2
                                 WHERE f2.fruitKey = f1.fuitKey + 1

The result looks like this.

fruitKey_low      fruitKey_hight
--------------       -----------------
2                       4
7                       9
13                     18

If you are creating the insert statements manually, you can pick the values from the above result as your fruitKeys. But most likely, you are going to write a program to create the insert statements and submit them to the database automatically. In this case, you need the SQL below to get the next missing fruitKey.

WITH G AS
(
SELECT (f1.fruitKey + 1) AS fruitKey_low,
       (select min(f3.fruitKey) - 1 from FRUIT f3
            where f3.fruitKey > f1.fruitKey) AS fruitKey_high
FROM FRUIT f1
WHERE NOT EXISTS (SELECT * FROM FRUIT f2
                                 WHERE f2.fruitKey = f1.fuitKey + 1
),
M AS
(
SELECT CASE
      WHEN (fruitKey_high - fruitKey_low >= 0) and (fruitKey_low > <current missing key>)                  THEN fruitKey_low
      WHEN (fruitKey_high - fruitKey_low > 0) and (fruitKey_low <= <current missing key>)
             and (fruitKey_high > <current missing key>)
      THEN <current missing key> + 1
      ELSE (select max(fruitKey)+1 from FRUIT)
      END AS fruitKey
FROM G
)
SELECT nvl (MIN(fruitKey), (select max(fruitKey)+1 from FRUIT)) AS fruitKey FROM M;

---------------------------------------------------------------------------------------------------

                        

If you have ever asked yourself these questions, this is the book for you. What is the meaning of life? Why do people suffer? What is in control of my life? Why is life the way it is? How can I stop suffering and be happy? How can I have a successful life? How can I have a life I like to have? How can I be the person I like to be? How can I be wiser and smarter? How can I have good and harmonious relations with others? Why do people meditate to achieve enlightenment? What is the true meaning of spiritual practice? Why all beings are one? Read the book free here.