Ok. Some confusion that I see here.
You list of (1,2,3,...,1000) does actually act as a table. If you have an actual list of numbers here (there are betters ways of generating a table like this) then use this as your select table. You will have to name the column if you wish to use it as the select
select ColA
from (
select 1 as colA
union select 2
union select 3
...
union select 1000)
where ColA not in(
select t.ColA from table t)
I hope you see how awful this could be if you have to enter the sequence each time, and
not in
is not the most efficient method of discerning the missing values.
You can set up a tabular function that takes the list of numbers as, for example, a csv and returns a table, or you can use a Common Table Expression to set up the table on the fly. If you have a set 1 to n range then I would create this with a CTE.
The query below would be how I would do it:
DECLARE @UpperLimit int
set @UpperLimit = 1000
WITH mycte AS (
SELECT 1 AS ColA
UNION ALL SELECT ColA+ 1 FROM mycte
WHERE ColA < @UpperLimit
)
SELECT m.ColA
FROM mycte m
WHERE NOT EXISTS (SELECT * FROM table1 t where m.ColA = t.ColA)
SELECT m.ColA
FROM TableFromCSVFunction(@TheCsvNumbers) m
WHERE NOT EXISTS (SELECT * FROM table1 t where m.ColA = t.ColA)
I hope that helps ^_^
Andy