CODEWITHSUNDEEP
mysql
Thomas
Thomas

Reputation: 10669

How to create a table with consecutive numbers

I need a helper table with only one int(11) column, that contains a row for each consecutive number from 1 to a given max. Can this be done with pure SQL?

Example:

INSERT INTO `helper`('itnum') VALUES (1),(2),(3),...(999999),(1000000);

I need a statement like this, but without explicitly listing all the entries to be made.

Upvotes: 0

Views: 436

Answers (2)

Ahmed Laatabi
Ahmed Laatabi

Reputation: 927

i think to do this, you have to execute your insert inside a loop in your SGBD procedure, or outside (php script, ...).

Upvotes: 1

davidethell
davidethell

Reputation: 12018

How about something like this:

DELIMITER |
DROP PROCEDURE IF EXISTS insert_helper_records |
CREATE PROCEDURE insert_helper_records(a_max INTEGER)
BEGIN
    DECLARE v_iteration INTEGER;
    SET v_iteration := 1;
    insert_loop: LOOP
        INSERT INTO helper(itnum) VALUES (v_iteration);
        SET v_iteration := v_iteration + 1;
        IF v_iteration = a_max THEN
            LEAVE insert_loop;
        END IF;
    END LOOP;
END |
DELIMITER ;

Then call it however you want like:

SELECT insert_helper_records(999999) FROM DUAL;

Upvotes: 1

Related Questions