Write a program to generate Sequence number using RECURSIVE CTE LOOP

Answer:

--*********** THE QUERY BEGINS ************************
DECLARE @maxLimit INT = 1000000

;WITH NumCTE AS(
SELECT Rn = 1
UNION ALL
SELECT Rn+1
FROM NumCTE WHERE Rn < @maxLimit)
SELECT *
FROM NumCTE
OPTION(MAXRECURSION 0)

--*********** THE QUERY ENDS ************************

In the above query, we are starting with number 1 and then in the recursive part the CTE moves on going till it reaches the @maxLimit. And at every step, the merging of the values is happening through the UNION ALL.

Finally the result is displayed outside the CTE.