How to generate a range of numbers between two num

Question

I have two numbers as input from the user, like for example 1000 and 1050.

How do I generate the numbers between these two numbers, using a sql query, in seperate rows? I want this:

 1000
 1001
 1002
 1003
 .
 .
 1050

Answer 1

This will also do

DECLARE @startNum INT = 1000;
DECLARE @endNum INT = 1050;
INSERT  INTO dbo.Numbers
        ( Num
        )
        SELECT  CASE WHEN MAX(Num) IS NULL  THEN @startNum
                     ELSE MAX(Num) + 1
                END AS Num
        FROM    dbo.Numbers
GO 51

Answer 2

2 years later, but I found I had the same issue. Here is how I solved it. (edited to include parameters)

DECLARE @Start INT, @End INT
SET @Start = 1000
SET @End = 1050

SELECT  TOP (@End - @Start+1) ROW_NUMBER() OVER (ORDER BY S.[object_id])+(@Start - 1) [Numbers]
FROM    sys.all_objects S WITH (NOLOCK)

Answer 3

slartidan's answer can be improved, performance wise, by eliminating all references to the cartesian product and using ROW_NUMBER() instead (execution plan compared):

SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n FROM 
(VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) x1(x),
(VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) x2(x),
(VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) x3(x),
(VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) x4(x),
(VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) x5(x)
ORDER BY n

Wrap it inside a CTE and add a where clause to select desired numbers:

DECLARE @n1 AS INT = 100;
DECLARE @n2 AS INT = 40099;
WITH numbers AS (
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n FROM 
    (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) x1(x),
    (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) x2(x),
    (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) x3(x),
    (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) x4(x),
    (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) x5(x)
)
SELECT numbers.n
FROM numbers
WHERE n BETWEEN @n1 and @n2
ORDER BY n

Answer 4

-- Generate Numeric Range
-- Source: http://www.sqlservercentral.com/scripts/Miscellaneous/30397/

CREATE TABLE #NumRange(
    n int
)

DECLARE @MinNum int
DECLARE @MaxNum int
DECLARE @I int

SET NOCOUNT ON

SET @I = 0
WHILE @I <= 9 BEGIN
    INSERT INTO #NumRange VALUES(@I)
    SET @I = @I + 1
END


SET @MinNum = 1
SET @MaxNum = 1000000

SELECT  num = a.n +
    (b.n * 10) +
    (c.n * 100) +
    (d.n * 1000) +
    (e.n * 10000)
FROM    #NumRange a
CROSS JOIN #NumRange b
CROSS JOIN #NumRange c
CROSS JOIN #NumRange d
CROSS JOIN #NumRange e
WHERE   a.n +
    (b.n * 10) +
    (c.n * 100) +
    (d.n * 1000) +
    (e.n * 10000) BETWEEN @MinNum AND @MaxNum
ORDER BY a.n +
    (b.n * 10) +
    (c.n * 100) +
    (d.n * 1000) +
    (e.n * 10000) 

DROP TABLE #NumRange

Answer 5

I had to insert picture filepath into database using similar method. The query below worked fine:

DECLARE @num INT = 8270058
WHILE(@num<8270284)
begin
    INSERT  INTO [dbo].[Galleries]
    (ImagePath) 
    VALUES 
    ('~/Content/Galeria/P'+CONVERT(varchar(10), @num)+'.JPG')

    SET @num = @num + 1
end

The code for you would be:

DECLARE @num INT = 1000
WHILE(@num<1051)
begin
    SELECT @num

    SET @num = @num + 1
end

Answer 6

declare @start int = 1000 declare @end int =1050

;with numcte
AS
(
SELECT @start [SEQUENCE]

UNION all

SELECT [SEQUENCE] + 1 FROM numcte WHERE [SEQUENCE] < @end )

SELECT * FROM numcte