sql - Select all ids that dont exist in database table

Question

I have a table that has 2 columns.

Id, ItemValue,

the id range is between 400000 and 409684.

The id is auto incremented by sql server but there are some gaps in the ids.

For example

400001
400002
400003
400005 

so in this case 400004 is missing.

Is there any way to get all these ids that dont exist?

Can I do some sort of select statement where i can go through all the ids and select the item value and where there is no id, have a 0 for item value?

Answer 1

Try this

WITH IDRange AS(
Select 400000 AS ID
UNION ALL
SELECT ID+1
FROM IDRange
WHERE ID <= 409684
)
SELECT IR.ID FROM IDRange IR
LEFT OUTER JOIN <your_table> YT
ON IR.ID=YT.ID
WHERE YT.ID IS NULL
OPTION(MAXRECURSION 0)