Creating a range of numbers in an sql subquery

Question

Is there a better way to generate a range of numbers in SQL than below? I'm using MySql.

SELECT tens.x + ones.x + 1
FROM
(SELECT 0 x UNION ALL
 SELECT 1 x UNION ALL
 SELECT 2 x UNION ALL
 ...
 SELECT 9 x ) ones

CROSS JOIN

(SELECT 0 x UNION ALL
 SELECT 10 x UNION ALL
 SELECT 20 x UNION ALL
 ...
 SELECT 90 x ) tens;

Answer 1

Using Sql server 2005+ you can make use of CTEs

DECLARE @Start INT, @End INT

SELECT  @Start = 0, @End = 100000

;WITH Numbers AS (
     SELECT @Start Num
     UNION ALL
     SELECT Num + 1
     FROM Numbers
     WHERE Num < @End
)
SELECT  *
FROM    Numbers
OPTION (MAXRECURSION 0);

Answer 2

A common way to do that in Oracle is to abuse the rownum pseudocolumn:

select rownum from all_objects where rownum<=100;

Answer 3

PostgreSQL allows you to use:

select * from generate_series(2,4);
 generate_series
-----------------
               2
               3
               4

That is specific for the PostgresSQL engine. But it shouldn't be to hard to write a stored proedure for your data base.

Answer 4

Why not loop? like

BEGIN
      DECLARE a INT Default 0 ;
      simple_loop: LOOP
         SET a=a+1;
         insert into mytable(id) values(a);
         IF a=1000 THEN
            LEAVE simple_loop;
         END IF;
   END LOOP simple_loop;

Modified from Mysql For Loop

Pardon me if the syntax is wrong as I am a pure SQL SERVER guy(: