Posted by Erland Sommarskog on 09/30/76 12:01

Yo mama (aaa@aaa.aaa) writes:
> Is there any way to insert into table 100 integers from 1 to 100 in one
> insert statement?
> Now I have to use loop to insert numbers. I was wondering if there is the
> simpler way.

WITH numbers(n) AS
SELECT row_number() OVER (ORDER BY object_id) FROM sys.columns
)
SELECT n FROM numbers WHERE n <= 100

The row_number function is handy for a lot of things.

If you want lots of numbers, you not have a good table to work from. Here
is a query for a million numbers:

CREATE TABLE Numbers (Number int NOT NULL PRIMARY KEY);
WITH digits (d) AS (
SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION
SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION
SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION
SELECT 0)
INSERT Numbers (Number)
SELECT Number
FROM (SELECT i.d + ii.d * 10 + iii.d * 100 + iv.d * 1000 +
v.d * 10000 + vi.d * 100000 AS Number
FROM digits i
CROSS JOIN digits ii
CROSS JOIN digits iii
CROSS JOIN digits iv
CROSS JOIN digits v
CROSS JOIN digits vi) AS Numbers
WHERE Number > 0



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]