insert record for 1 to 100000

how to create record from 1 to 100000 in sqljoe
RANDOM record? Loop?
"joe" <joe@.discussions.microsoft.com> wrote in message
news:4DBB01BC-8B29-48E6-B148-C58F5030E20F@.microsoft.com...
> how to create record from 1 to 100000 in sql|||Google for "number table". There are plenty of examples.
ML
http://milambda.blogspot.com/|||is it for a table with values from 1 to 10000 ?|||How about :
DROP TABLE #control
CREATE TABLE #control ( control_no INT PRIMARY KEY )
SET NOCOUNT ON
INSERT INTO #control VALUES ( 0 )
INSERT INTO #control VALUES ( 1 )
INSERT INTO #control VALUES ( 2 )
INSERT INTO #control VALUES ( 3 )
INSERT INTO #control VALUES ( 4 )
INSERT INTO #control VALUES ( 5 )
INSERT INTO #control VALUES ( 6 )
INSERT INTO #control VALUES ( 7 )
INSERT INTO #control VALUES ( 8 )
INSERT INTO #control VALUES ( 9 )
SET NOCOUNT OFF
SELECT x.x + 1
FROM
(
SELECT DISTINCT
CAST( c1.control_no AS CHAR(1) ) +
CAST( c2.control_no AS CHAR(1) ) +
CAST( c3.control_no AS CHAR(1) ) +
CAST( c4.control_no AS CHAR(1) ) +
CAST( c5.control_no AS CHAR(1) ) AS x
FROM #control c1, #control c2, #control c3, #control c4, #control c5
) x
WHERE x.x Between 9 And 100000
ORDER BY 1
Let me know how you get on.
Damien
"joe" wrote:

> how to create record from 1 to 100000 in sql|||Sorry,
meant that to be:
DROP TABLE #control
CREATE TABLE #control ( control_no INT PRIMARY KEY )
SET NOCOUNT ON
INSERT INTO #control VALUES ( 0 )
INSERT INTO #control VALUES ( 1 )
INSERT INTO #control VALUES ( 2 )
INSERT INTO #control VALUES ( 3 )
INSERT INTO #control VALUES ( 4 )
INSERT INTO #control VALUES ( 5 )
INSERT INTO #control VALUES ( 6 )
INSERT INTO #control VALUES ( 7 )
INSERT INTO #control VALUES ( 8 )
INSERT INTO #control VALUES ( 9 )
SET NOCOUNT OFF
INSERT INTO #control
SELECT x.x + 1
FROM
(
SELECT DISTINCT
CAST( c1.control_no AS CHAR(1) ) +
CAST( c2.control_no AS CHAR(1) ) +
CAST( c3.control_no AS CHAR(1) ) +
CAST( c4.control_no AS CHAR(1) ) +
CAST( c5.control_no AS CHAR(1) ) AS x
FROM #control c1, #control c2, #control c3, #control c4, #control c5 --,
#control c6
) x
WHERE x.x Between 9 And 100000
SELECT *
FROM #control
ORDER BY 1
but you get the idea! ; )
Damien|||Someone just did a time test on various solutions for this problem.
The best version was something like this:
SELECT Units.nbr + Tens.nbr + Hundreds.nbr
FROM (SELECT nbr FROM Digits) AS Units(nbr)
CROSS JOIN
(SELECT nbr * 10 FROM Digits) AS Tens(nbr)
CROSS JOIN
(SELECT nbr * 100 FROM Digits) AS Hundreds(nbr);
The idea was to build the decimal places once inthe FROm clause, then
do simple addition in the SELECT.