How to Generate Dynamic Range of Sequence Numbers in SQL Server

How to Generate specific Range of Sequence Numbers in SQL Server

Scenario:

Suppose we would like to generate the Sequence of numbers between the specified Start and End ranges. ( Eg: Say Numbers between 5 and 15 )

We can achieve this by using the Recursive CTE feature of SQL, as discussed below.

CTE (common table expression):

The CTE (common table expression) is an SQL feature will be defined using WITH Clause, that returns a temporary data set which can be used by another query. The scope of CTE is limited to the Current query.

Syntax:

WITH cte_name

AS

(

   cte_query_definition

)

SELECT * FROM cte_name;

Recursive CTE:

Since it’s a temporary result, will not stored anywhere, but it still can be referenced like you would reference any other table.

A Recursive CTE references itself. It returns the result subset, then it repeatedly (recursively) references itself, and stops when it returns all the results.

Syntax:

WITH cte_name

AS

(

    cte_query_definition (or) initial query  -- Anchor member

    UNION ALL

    recursive_query with condition     -- Recursive member

)

SELECT * FROM cte_name

Example :

Generating the Numbers between 5 to 15 using the Recursive CTE method.

/* Variable declaration method:

DECLARE @StartNum INT , @EndNum INT ;

Select @StartNum=1, @EndNum=10000

*/

DECLARE @StartNum INT=5

DECLARE @EndNum INT=15

;

WITH Seq_RecCTE

AS 

(

    SELECT @StartNum AS Num

    UNION ALL

    SELECT sn.Num+1 FROM Seq_RecCTE  sn

WHERE sn.Num+1<=@EndNum

)

SELECT * FROM Seq_RecCTE

Option (MaxRecursion 32767) ;

/* To insert the result to a New Table :

SELECT * INTO tbl_SeqNumbers

FROM Seq_RecCTE

Option (MaxRecursion 32767) ;

*/

Result:

--------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

--------------------------------------------------------------------------------------------------------