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/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx
Here is a method using the system table spt_values
(http://www.sqlmag.com/Articles/ArticleID/22920/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