How To: Duplicate Rows using SQL in MySQL

Are you looking to simply duplicate existing rows in your MySQL table? I needed to do this recently for a database I had for a project I was developing, I wanted to duplicate the data in the database to about 10,000 rows to test response times of SQL and perform optimization on the system.

I had a little difficulty working this out because most of the internet talks about how to AVOID or DELETE duplicate rows, not create them :p

So anyway after some searching I found out how to do this with a simple SQL  command:

insert into `MyTable` SELECT * FROM `MyTable`

This query will select all columns from the table and insert them back into the table, so effectively you will duplicate everything in the table.

For example say we had the following data in MyTable:

After performing the above SQL command we would end up with:

You can do normal WHERE commands in the select to duplicate specific rows if you wish.

insert into `MyTable` SELECT * FROM `MyTable` WHERE `title` = 'mr'

This would result in:

Problems with Duplicate Keys

There is a big problem with the above SQL, if you have any columns that are auto-increment and/or do not allow duplicate information then obviously it will not work, for example see below:

In the above example we have an auto-increment id field, the rows in this table cannot be duplicated using the above SQL command because of this, what you need to do is specify which columns to use in the duplication process, by not selecting the id we can avoid the issue completely:

insert into reviews (`name`, `description`, `title`) SELECT `name`, `description`, `title` FROM reviews;

The above SQL will result in the following data being inserted into the table:

Edit: 29-03-14 - A faster way?

A user named Kenoli has commented on this post with what might be an even faster approach to duplicating rows with MySQL, try it out and let us know if either solution has helped you!

CREATE TEMPORARY TABLE tmptable_1 SELECT * FROM table WHERE primarykey = 1;
UPDATE tmptable_1 SET primarykey = NULL;
INSERT INTO table SELECT * FROM tmptable_1;
DROP TEMPORARY TABLE IF EXISTS tmptable_1;

Note: that this assumes an auto increment primary key. You can leave out line 2 if the row does not have one. The last row can be omitted as the temporary table will be deleted when the user logs out.

Author: Dean WilliamsI'm a Web Developer, Graphics Designer and Gamer, this is my personal site which provides PHP programming advice, hints and tips

Twitter Facebook Linkedin