How to generate numeric sequences in SQL without having to create tables?

In these cases, a performatic solution is to use generators. The initial solution works on almost any DBMS with SQL support. See the end of the answer for solutions optimized for specific DBMSs.

Here is an example that produces numbers from 0 to 999:

SELECT d1+d2*10+d3*100 AS gerador FROM
   ( SELECT 0 AS d1 UNION 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 ) t1,
   ( SELECT 0 AS d2 UNION 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 ) t2,
   ( SELECT 0 AS d3 UNION 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 ) t3

See working in SQL Fiddle .

Operation:

• Each of the SELECT 0 AS d1 UNION SELECT 1 ... SELECT 9 generates numbers from 0 to 9;

• What we did in the initial example was to combine 3 of them, to have 3 decimal places. The d1+d2*10+d3*100 formula converts the 3 outputs of each generator into an integer.

Depending on the DBMS you may need to use UNION ALL instead of just UNION .

Optimizing for more specific uses:

• If you need numbers from 1 to 780, simply adjust the formula and use LIMIT 780 , for example.

• Nothing prevents you from avoiding multiplication by swapping the subqueries:

  SELECT d1 + d2 AS gerador FROM
   ( SELECT 0 AS d1 UNION 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 ) t1,
   ( SELECT 0 AS d2 UNION SELECT 10 UNION SELECT 20 UNION SELECT 30 UNION SELECT 40
     UNION SELECT 50 UNION SELECT 60 UNION SELECT 70 UNION SELECT 80 UNION SELECT 90 ) t2
  

  In this example the second set of SELECT s is optimized with the * 10 numbers already.

• If you need numbers from 0 to 255, it's much better to use a shorter query:

  SELECT d1+d2*4+d3*16+d4*64 AS gerador FROM
     ( SELECT 0 AS d1 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3) t1,
     ( SELECT 0 AS d2 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3) t2,
     ( SELECT 0 AS d3 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3) t3,
     ( SELECT 0 AS d4 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3) t4
  

  In this case the logic was as follows: 4 ^ 4 = 256, so we used 4 queries , but with 4 digits each. The important thing is to understand the operation of the generator, as there are several ways to optimize the code.

• Generating dates

  We can restrict the 3rd digit of the initial example to only 3, since 0 to 399 is enough to cover a whole year. DATE_ADD is a solution for generating a date:

  DATE_ADD( '2014-01-01', INTERVAL d1+d2*10+d3*100 DAY )
  

  and a WHERE is enough to restrict output to the one-year interval. See the other question that you mentioned a response with the complete example.

Proprietary solutions:

MS SQL Server

In MS SQL Server we can simplify a bit using a function. Note the care of limiting recursion with MAXRECURSION 0 :

WITH gerador (id) AS (
     SELECT 1
     UNION ALL
     SELECT id + 1
     FROM gerador
     WHERE id < 1000000
  )
  SELECT TOP 1000 id FROM gerador
  OPTION ( MAXRECURSION 0 )
GO

See working on SQL Fiddle .

PostgreSQL

Simpler still, with the function generate_series :

SELECT * FROM generate_series ( 1, 1000 )

See working in SQL Fiddle .

Oracle

Oracle has a special dual calling table in the default installation, with a series of special-purpose pseudo-columns. Example for our question:

SELECT level FROM dual CONNECT BY level < 1000

See working in SQL Fiddle .

Supplementary answer:

Initially the question puts the condition of not having a table for this. Anyway, an intermediate solution, but very interesting if these sequences are necessary on many occasions, you have a table of integers:

CREATE TABLE inteiros (i INT);
INSERT INTO inteiros (i) VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);

So you can use the techniques above by combining this table instead of SELECT UNION :

SELECT d3.i*100+d2.i*10+d1.i+1 AS gerador
FROM inteiros AS d1
JOIN inteiros AS d2
JOIN inteiros AS d3;

What you should do and create a SEQUENCE that has compatibility:

  

The CREATE SEQUENCE command conforms to the SQL standard, with the following exceptions:

     

• The expression of the AS
  
• Obtaining the next value is done using the nextval ()   instead of the expression of the NEXT VALUE FOR pattern.
  
• The OWNED BY clause is an extension of PostgreSQL.
  

At this link: Create Sequence has a detailed PostgreSQL 8.2.0 Documentation explaining and exemplifying possible cases of Sequence creation.

SEQUENCES are objects of the DBMS to generate auto-numbering values, where we can define the initial values and their increments.

Here is an example of sequence creation in Oracle:

create sequence SEQ_EXAMPLE
  minvalue 1
  maxvalue 999
  start with 1
  increment by 1
  nocache
  nocycle;

Your parameters mean respectively that the minimum value of 1 (minvalue 1), the maximum of 999 (maxvalue 999), starts at 1 (start with 1), will be incremented by 1 (increment by 1), will not preallocate values for this sequence (nocache) and when reaching the maximum will not go to the minimum value (no cycle).

To get the current value of the sequence:

 SELECT SEQ_EXAMPLE.currval

To get the next value of the sequence:

SELECT SEQ_EXAMPLE.nextval

To use it in creating your table, in this case as a key:

INSERT INTO exemplo_tb (NR_SEQUENCIA, NOME, LOGIN)
VALUES (SEQ_EXAMPLE.Nextval, 'Fulaninho', 'ful');