Select Union All as a cartesion product. I normally create for example a set of 4mrd records and use the numeric values to add columns and generate data based on the value. I often use modulo (%) to narrow down certain values.
with E00(N) AS (SELECT 1 UNION ALL SELECT 1),
E02(N) AS (SELECT 1 FROM E00 a, E00 b),
E04(N) AS (SELECT 1 FROM E02 a, E02 b),
E08(N) AS (SELECT 1 FROM E04 a, E04 b),
E16(N) AS (SELECT 1 FROM E08 a, E08 b),
E32(N) AS (SELECT 1 FROM E16 a, E16 b), -- 4.294.967.296
-- E64(N) AS (SELECT 1 FROM E32 a, E32 b),
-- E128(N) AS (SELECT 1 FROM E64 a, E64 b),
cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E32)
SELECT max(N)
FROM cteTally
-- WHERE N <= 2000000000;
Or ..
;WITH e1(n) AS
(
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1
), -- 10
e2(n) AS (SELECT 1 FROM e1 CROSS JOIN e1 AS b), -- 10 x 10 = 100
e3(n) AS (SELECT 1 FROM e1 CROSS JOIN e2 AS b) -- 10 X 100 = 1000
--e4(n) AS (SELECT 1 FROM e3 CROSS JOIN e3 AS b),
--e5(n) AS (SELECT 1 FROM e4 CROSS JOIN e4 AS b),
--e6(n) AS (SELECT 1 FROM e5 CROSS JOIN e5 AS b),
--e7(n) AS (SELECT 1 FROM e6 CROSS JOIN e6 AS b)
SELECT n = row_number() OVER (ORDER BY n)
from e3 ORDER BY n;
Semomething like this..
;WITH e1(n) AS
(
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1
), -- 10
e2(n) AS (SELECT 1 FROM e1 CROSS JOIN e1 AS b), -- 10 x 10 = 100
e3(n) AS (SELECT 1 FROM e1 CROSS JOIN e2 AS b) -- 10 X 100 = 1000
SELECT n = row_number() OVER (ORDER BY n)
INTO dbo.t1
from e3 ORDER BY n;
SELECT value
FROM GENERATE_SERIES(1, 10);