CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Justin
Justin

Reputation: 972

Temp table of numbers that do not exist in another table

I have a table called UsedNumbers

UsedNumbers contains number in the range of 1 AND 9999. The numbers are can be anywhere in this range.

I want to create a temp table #UnUsedNumbers.

So far I found code create a range of numbers from 1 to 9999. But I'm not entirely sure how to INSERT this into a temp table and then extract only the numbers that do not exist in UsedNumbers.

;WITH x AS
(
    SELECT TOP (224) [OBJECT_ID] FROM sys.all_objects
)
SELECT TOP (9999) n = ROW_NUMBER() OVER (ORDER BY x.[object_id])
FROM x CROSS JOIN x AS y
ORDER BY n;

I would love to understand more about this if someone could help.

Upvotes: 0

Views: 70

Answers (2)

Mani
Mani

Reputation: 1

try this code 

;WITH NumberCTE AS
(
    SELECT TOP (224) [OBJECT_ID] FROM sys.all_objects
)
,
AllNumbersCTE AS
(
    SELECT TOP (9999)
        ROW_NUMBER() OVER(order by RS1.[OBJECT_ID]) AS ROWID
    FROM 
        NumberCTE AS RS1
    LEFT JOIN NumberCTE AS RS2 ON 1 = 1
)

SELECT ROWID FROM AllNumbersCTE WHERE ROWID NOT IN (SELECT Number FROM UsedNumbers)

Upvotes: 0

Steve Ford
Steve Ford

Reputation: 7763

You could try something like:

;WITH x AS
(
    SELECT TOP (224) [OBJECT_ID] FROM sys.all_objects
)
SELECT TOP (9999) n = ROW_NUMBER() OVER (ORDER BY x.[object_id])
FROM x CROSS JOIN x AS y
EXCEPT 
SELECT num
FROM UsedNumbers
ORDER BY n

Upvotes: 1

Related Questions