Inserting 600 MYSQL rows from 400 to 1000

Question

Hi everyone I'm trying to insert 600 rows in a MYSQL table starting from 400 to 1000 using:

INSERT INTO
    myTable
    (
    nr
    )
SELECT
    SEQ.SeqValue
FROM
(
SELECT
    (HUNDREDS.SeqValue + TENS.SeqValue + ONES.SeqValue) SeqValue
FROM
    (
    SELECT 0  SeqValue
    UNION ALL
    SELECT 1 SeqValue
    UNION ALL
    SELECT 2 SeqValue
    UNION ALL
    SELECT 3 SeqValue
    UNION ALL
    SELECT 4 SeqValue
    UNION ALL
    SELECT 5 SeqValue
    UNION ALL
    SELECT 6 SeqValue
    UNION ALL
    SELECT 7 SeqValue
    UNION ALL
    SELECT 8 SeqValue
    UNION ALL
    SELECT 9 SeqValue
    ) ONES
CROSS JOIN
    (
    SELECT 0 SeqValue
    UNION ALL
    SELECT 10 SeqValue
    UNION ALL
    SELECT 20 SeqValue
    UNION ALL
    SELECT 30 SeqValue
    UNION ALL
    SELECT 40 SeqValue
    UNION ALL
    SELECT 50 SeqValue
    UNION ALL
    SELECT 60 SeqValue
    UNION ALL
    SELECT 70 SeqValue
    UNION ALL
    SELECT 80 SeqValue
    UNION ALL
    SELECT 90 SeqValue
    ) TENS
CROSS JOIN
    (
    SELECT 400 SeqValue
    UNION ALL
    SELECT 500 SeqValue
    UNION ALL
    SELECT 600 SeqValue
    UNION ALL
    SELECT 700 SeqValue
    UNION ALL
    SELECT 800 SeqValue
    UNION ALL
    SELECT 900 SeqValue
    ) HUNDREDS
) SEQ

I keep getting the results in the order:

'968'
'469'
'569'
'669'

etc...

How can I sort it out?

Is this a real world use case? Might it not be easier to write a script that generates 1000 mySQL INSERT statements? — Pekka, Dec 27 '11 at 15:17
Are you trying to insert **random** 600 numbers in the range [400, 1000) or are you trying to insert all the numbers in that range? — melihcelik, Dec 27 '11 at 15:21
What the hell is that query? There's no way that can be real world case, it's simply.. terrible. There are at least two different ways that generate sequential values (and write in that order) which are both easier to implement and faster to execute. — N.B., Dec 27 '11 at 15:23
There are currently 399 records in the table so I'm trying insert empty records in the range 400-1000 (i.e. records 400, 401, 402, 403 to 1000). Thanks! — methuselah, Dec 27 '11 at 15:26
@methuselah - your comment explained what you're trying to do. I'm assuming you're not "satisfied" with how MySQL's auto_increment is not being reused and then you get gaps. If my assumption is wrong, feel free to correct me. But if I am right, I'll just say what I always say when I see people doing this - just don't. Don't touch the auto_increment column yourself. Create another column that you'll use for ordering or whatever and change that one via triggers. — N.B., Dec 27 '11 at 15:55
I based it on this: http://stackoverflow.com/questions/4202979/mysql-query-to-insert-300-rows-at-once/4203209#4203209 — methuselah, Dec 27 '11 at 15:59

score 2 · Accepted Answer · answered Dec 27 '11 at 15:26

2

select @s:=@s+1 as seq
FROM (SELECT @s:=399) AS baseview, some_table
WHERE @s<1000
;

assuming some_table has at least 401 rows.

If you really want to go with your construct, add

ORDER BY SeqValue

at the end.

answered Dec 27 '11 at 15:26

Eugen Rieck

64,175
10
70
92

@s is a sesion variable, it is initialized by SELECT @s:=399 and then incremented on each row. baseview is just a random name I gave to "SELECT @s:=399" as MySQL insists on each table having a unique name – Eugen Rieck Dec 27 '11 at 15:42
How do I sort in ascending order cos it seems to return tables in descending order? – methuselah Dec 27 '11 at 15:55

Inserting 600 MYSQL rows from 400 to 1000

1 Answers1