Create Temp Table with Range of Numbers

Question

I have a table that has rows with the start and end numbers of a range e.g.

key     startID       endID
 1         500        505
 2         784        788
 3         802        804

and so on..

I would like to create a temp table (or table variable/cte etc) that has a row for each of these numbers and the range they cover between them - i.e. given the above example I would like to see a table with the following rows:

ID
500
501
502
503
504
505
784
785
786
787
788
802
803
804

Can anyone point me in the direction of quick and easy way to achieve this? I thought about using a numbers table somehow but the tables I am looking at have > 200m rows and I don't have a numbers table that big!

Any help is much appreciated. Thanks in advance.

Answer 1

WITH    q AS
        (
        SELECT  startId, endId
        FROM    ranges
        UNION ALL
        SELECT  startId + 1, endId
        FROM    q
        WHERE   startId < endId
        )
SELECT  startId
FROM    q
OPTION  (MAXRECURSION 0)

Answer 2

In MSSQL, you can also use select from any arbitrary large table, syscolumns would be an example. If there are not enough rows - you can do a cross join:

SELECT        TOP 10000
ROW_NUMBER() OVER (ORDER BY c1.id)
FROM          syscolumns AS c1
CROSS JOIN    syscolumns AS c2