generating sequential numbers 1 to 10000 using CROSS JOIN in SQL Server

Bandi
Posted by Bandi under Sql Server category on | Points: 40 | Views : 606
Some times we may need to generate a list of sequential numbers to use in some scenarios...

The below code generated 1 to 1000 numbers..
;WITH CteDigits AS (SELECT * FROM (Values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) as t(Digit))
SELECT D3.digit * 100 + D2.digit * 10 + D1.digit + 1 AS n
FROM CteDigits AS D1
CROSS JOIN CteDigits AS D2
CROSS JOIN CteDigits AS D3
ORDER BY n;


Generating 1 to 10000 numbers...

;WITH CteDigits AS (SELECT * FROM (Values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) as t(Digit))
SELECT D4.digit*1000 + D3.digit * 100 + D2.digit * 10 + D1.digit + 1 AS n
FROM CteDigits AS D1
CROSS JOIN CteDigits AS D2
CROSS JOIN CteDigits AS D3
CROSS JOIN CteDigits AS D4
ORDER BY n;

Comments or Responses

Login to post response