Just Code‎ > ‎

TSQL - Generate series of numbers on the fly using this CTE, useful for data generation scripts

posted Mar 9, 2012, 1:46 AM by Peter Henell   [ updated Mar 15, 2012, 5:49 AM ]
Just add more cross joins to m2 in the m3 CTE if you want even more generated rows.

SET nocount ON
;

WITH m1 AS(
    SELECT 1 AS n
    UNION ALL
    SELECT 1
),
m2 AS (
    SELECT m1.n FROM m1 CROSS JOIN m1 m12 CROSS JOIN m1 m13 CROSS JOIN m1 m14
),
m3 AS 
(
    SELECT ROW_NUMBER() OVER(ORDER BY a.n) AS n
    FROM m2 a
    CROSS JOIN 
    m2 b
    CROSS JOIN 
    m2 c
)

SELECT n FROM m3
Comments