Script to generate Nums table

Forever tracking this down so I thought I'd post it here so I can get it easily.


-- create table
CREATE TABLE dbo.nums (n INT NOT NULL PRIMARY KEY)
GO
DECLARE @rows INT

SET @rows = 10000
-- prime the table
INSERT INTO dbo.nums VALUES (1)

-- loop around while rows are being inserted
WHILE @@rowcount > 0
BEGIN
  INSERT dbo.nums
  SELECT t.n + x.MaxRowNum   FROM dbo.nums t
    CROSS JOIN (SELECT MAX(n) MaxRowNum FROM dbo.nums) x
  WHERE t.n <= @rows - x.MaxRowNum
END
GO