Insert n ordered numbers into a table

Question

I have a table I want to fill with data. I'm building a lookup table and I want to prime the data that will be included. The lookup table will have about 1400 rows so a standard multi insert does not seem correct. Additionally I want to prime this table without PHP if possible. This is the form of my table:

CREATE TABLE score_percentile_lookup (
    id INT(11) NOT NULL AUTO_INCREMENT,
    instance_score INT(11), 
    percentile DECIMAl(5, 2)
    PRIMARY KEY(id)
);

Basically, to start, instance score needs to be an incremented list [0 .. 1400], and percentile can be blank. Does mysql support syntax to run an insert that would basically look something like as followed:

INSERT into score_percentile_lookup (instance_score) VALUES (0), (1), ... (1400);

I could use PHP to generate this list but I want to know if its possible without it. Thank you to anyone who helps.

This ? made me realize CTEs are not available in MySQL and I became sad because that would be perfect here... — Doug Morrow, Sep 06 '13 at 13:43
possible duplicate of [Generating a range of numbers in MySQL](http://stackoverflow.com/questions/186756/generating-a-range-of-numbers-in-mysql) — Explosion Pills, Sep 06 '13 at 13:44
Definitely a duplicate. I'm glad I know about that other question now. — usumoio, Sep 06 '13 at 13:59

eggyal · Accepted Answer · 2013-09-06T14:05:06.107

There is no such syntax inherent in MySQL.

You can however use a looping construct within a stored program:

DELIMITER ;;

CREATE PROCEDURE insert_ints(lbound INT, ubound INT, step INT UNSIGNED) BEGIN
  PREPARE stmt FROM '
    INSERT INTO score_percentile_lookup (instance_score) VALUES (?)
  ';

  SET @acc := lbound;
  WHILE @acc <= ubound DO
    EXECUTE stmt USING @acc;
    SET @acc := @acc + step;
  END WHILE;
  SET @acc := NULL;

  DEALLOCATE PREPARE stmt;
END;;

DELIMITER ;

And then do:

CALL insert_ints(0,1400,1);

Alternatively, if you already have a table containing such a range of numbers, you could use INSERT ... SELECT:

INSERT INTO score_percentile_lookup (instance_score)
  SELECT num FROM numbers WHERE num BETWEEN 0 AND 1400;

score 1 · Answer 2 · edited May 23 '17 at 10:25

Adapted from excellent answer in related question:

INSERT INTO score_percentile_lookup
(instance_score)
SELECT TOP 1401
(TWO_1.SeqValue + TWO_2.SeqValue + TWO_4.SeqValue + TWO_8.SeqValue + TWO_16.SeqValue + TWO_32.SeqValue + TWO_64.SeqValue + TWO_128.SeqValue + TWO_256.SeqValue + TWO_512.SeqValue + TWO_1024.SeqValue) SeqValue
FROM
(SELECT 0 SeqValue UNION ALL SELECT 1 SeqValue) TWO_1
CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 2 SeqValue) TWO_2
CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 4 SeqValue) TWO_4
CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 8 SeqValue) TWO_8
CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 16 SeqValue) TWO_16
CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 32 SeqValue) TWO_32
CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 64 SeqValue) TWO_64
CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 128 SeqValue) TWO_128
CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 256 SeqValue) TWO_256
CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 512 SeqValue) TWO_512
CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 1024 SeqValue) TWO_1024
ORDER BY SeqValue

Insert n ordered numbers into a table

2 Answers2