# Brett Kaiser (x002548) Blog

Not Just a Number - Brett Kaiser

## SET BASED Number Table Create Hack

Well…don't think I've seen this hack before..I guess you might call it a setbased way to create a numbers table.

Who has done it this way before?

USE [dba] GO

CREATE TABLE n(n int) GO

INSERT INTO n(n) SELECT CONVERT(int,m.n + hth.n + tth.n + th.n + h.n + t.n + u.n) AS [myDay99] FROM (SELECT '0' AS [n] UNION SELECT '1' AS [n] UNION SELECT '2' AS [n] UNION SELECT '3' AS [n] UNION SELECT '4' AS [n] UNION
SELECT '5' AS [n] UNION SELECT '6' AS [n] UNION SELECT '7' AS [n] UNION SELECT '8' AS [n] UNION SELECT '9' AS [n] ) AS u CROSS JOIN (SELECT '0' AS [n] UNION SELECT '1' AS [n] UNION SELECT '2' AS [n] UNION SELECT '3' AS [n] UNION SELECT '4' AS [n] UNION
SELECT '5' AS [n] UNION SELECT '6' AS [n] UNION SELECT '7' AS [n] UNION SELECT '8' AS [n] UNION SELECT '9' AS [n] ) AS t CROSS JOIN (SELECT '0' AS [n] UNION SELECT '1' AS [n] UNION SELECT '2' AS [n] UNION SELECT '3' AS [n] UNION SELECT '4' AS [n] UNION
SELECT '5' AS [n] UNION SELECT '6' AS [n] UNION SELECT '7' AS [n] UNION SELECT '8' AS [n] UNION SELECT '9' AS [n] ) AS h CROSS JOIN (SELECT '0' AS [n] UNION SELECT '1' AS [n] UNION SELECT '2' AS [n] UNION SELECT '3' AS [n] UNION SELECT '4' AS [n] UNION
SELECT '5' AS [n] UNION SELECT '6' AS [n] UNION SELECT '7' AS [n] UNION SELECT '8' AS [n] UNION SELECT '9' AS [n] ) AS th CROSS JOIN (SELECT '0' AS [n] UNION SELECT '1' AS [n] UNION SELECT '2' AS [n] UNION SELECT '3' AS [n] UNION SELECT '4' AS [n] UNION
SELECT '5' AS [n] UNION SELECT '6' AS [n] UNION SELECT '7' AS [n] UNION SELECT '8' AS [n] UNION SELECT '9' AS [n] ) AS tth CROSS JOIN (SELECT '0' AS [n] UNION SELECT '1' AS [n] UNION SELECT '2' AS [n] UNION SELECT '3' AS [n] UNION SELECT '4' AS [n] UNION
SELECT '5' AS [n] UNION SELECT '6' AS [n] UNION SELECT '7' AS [n] UNION SELECT '8' AS [n] UNION SELECT '9' AS [n] ) AS hth CROSS JOIN (SELECT '0' AS [n] UNION SELECT '1' AS [n] UNION SELECT '2' AS [n] UNION SELECT '3' AS [n] UNION SELECT '4' AS [n] UNION
SELECT '5' AS [n] UNION SELECT '6' AS [n] UNION SELECT '7' AS [n] UNION SELECT '8' AS [n] UNION SELECT '9' AS [n] ) AS m ORDER BY 1 GO