Doubling MySQL rows

Need to duplicate existing table to increase rows for testing

[paddycallaghan]

I have a simple query that i want to perform on a table. Basically i want to increase the number of rows up to a million so that i can perform some tests on the table.

I need to do somwthing like

INSERT INTO `<tablename>` SELECT <all>, NULL, FROM `<tablename>` WHERE 1

[ReveL]

that should work, what is your question? ;)

[coopster]

Welcome to WebmasterWorld, paddycallaghan.
Welcome to WebmasterWorld, ReveL.

ReveL, you may have missed it here but the question is actually in the subtitle description of the message, "how do I duplicate an existing table to increase rows for testing?"

One option would be to load up your test data in a spreadsheet and use LOAD DATA INFILE [dev.mysql.com]. Off line entry like this is usually the quickest. However, you will more than likely be limited to the number of rows your spreadsheet program will allow. You could initially load the spreadsheet by dumping your current table to it, change the auto_increment key to "\N" and just keep reloading it. See the link for more details on inserting NULL values.

[ReveL]

ok, I still don't now if I got your question okay but you might want to use a temporary table:

CREATE TEMPORARY TABLE temptable SELECT * FROM orgtable;

if you then paste a lot of this in your mysql tool:

INSERT INTO orgtable SELECT * FROM temptable;
INSERT INTO temptable SELECT * FROM orgtable;
INSERT INTO orgtable SELECT * FROM temptable;
INSERT INTO temptable SELECT * FROM orgtable;

The number of rows will add up quite quickly, please remind that if you use phpmysqladmin or some other mysql tool you might want to leave the 'TEMPORARY' and delete the table manually afterwards.