How to insert 100,000 records in a single query in mySQL

Question

How to insert 100000+ records in a table in mySQL database.

What I think like :

BEGIN declare i=0,n=100000; while (i<n) DO i++; insert into tableName values (1,2,3); END while; END

Why I need : To test load on server if this much records are in database.

Is there any simple one line query which can work this?

This is likely better done with a scripting language. That would also allow you to generate random demo data. That being said 1000 records is nothing to any modern RDMS worth it's salt. — Jim
– Jim, Commented Apr 23, 2014 at 10:30
See this old chestnut... datacharmer.blogspot.co.uk/2006/06/… — Strawberry
– Strawberry, Commented Apr 23, 2014 at 10:43

Bohemian · Accepted Answer · 2014-04-23 10:42:35Z

9

Insert from the table back into itself:

insert into tableName values (1,2,3); insert into tableName select * from tableName; -- 2 rows insert into tableName select t.* from tableName t, tableName t2, tableName t3, tableName t4; -- raised to 4th power every execution insert into tableName select t.* from tableName t, tableName t2, tableName t3, tableName t4; -- raised to 4th power every execution

Now there are 104994 rows

See SQLFiddle

You can add as many copies of the table to the cross join in one command as you like to increase the power to which the quantity is raised.

answered Apr 23, 2014 at 10:42

Bohemian♦

427k103 gold badges603 silver badges750 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

unbreak Over a year ago

is it any way for changing vaules? eg. +100

Bohemian Over a year ago

@unbreak specifically what do you mean "+100"?

unbreak Over a year ago

@Bohemian, i have in DB one column with values 1,2,3,4....100, now i will do insert into tableName select * from tableName; but i want them to start with 101,102... not again 1,2.. Something like this: insert into tableName select code+100 from tableName;

unbreak Over a year ago

@Bohemian insert into test (code) SELECT code + 100 FROM test Works!

Bohemian Over a year ago

@unbreak this works more generally: insert into test select code + (select count(*) from test) from test. No matter how many rows you had, every execution would double the number of rows and correctly number code.

|

Reversed Engineer · Accepted Answer · 2015-07-24 07:20:02Z

Using a CTE, on my machine inserting 100,000 records takes 0.7 seconds. Personally I feel this is easier to customize, e.g. remove the comment on the last line to make it a million records. Add or remove references to the CTE for more or less records, in powers of 10.

WITH cteTen AS ( SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 ) INSERT INTO tableName SELECT 1, 2, 3 -- YOUR TABLE AND VALUES HERE FROM cteTen AS X1, cteTen AS X2, cteTen AS X3, cteTen AS X4, cteTen AS X5 --, cteTen AS X6

Hmmm - I didn't read the OP's tags - it's for MySQL. Not sure if MySQL supports CTEs yet???

Collectives™ on Stack Overflow

How to insert 100,000 records in a single query in mySQL

2 Answers 2

6 Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

1 Comment

Related