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

Post date: Mar 9, 2012 9:46:59 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