Hi,
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.

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/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Here is a method using the system table spt_values
(http://www.sqlmag.com/Articles/Artic...0/pg/2/2.html), which works
fine for small numbers table (note: the table is undocumented and assuming
unsupported). This works fine on SQL Server 2000, 2005, and the current 2008
CTP:

CREATE TABLE Numbers (nbr INT NOT NULL PRIMARY KEY)

INSERT INTO Numbers ( nbr )
SELECT number FROM master..spt_values
WHERE type = 'P'
AND number BETWEEN 1 AND 100

HTH,

Plamen Ratchev
http://www.SQLStudio.com