How To: Duplicate Rows using SQL in MySQL

There is a Mobile Optimized version of this page (AMP). Open Mobile Version.

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:

name

description

title

adamsillymr
fredfunnymr
jennymadmrs

 

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

name

description

title

adamsillymr
fredfunnymr
jennymadmrs
adamsillymr
fredfunnymr
jennymadmrs

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:

name

description

title

adamsillymr
fredfunnymr
jennymadmrs
adamsillymr
fredfunnymr

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:

id

name

description

title

1adamsillymr
2fredfunnymr
3jennymadmrs

 

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:

id

name

description

title

1adamsillymr
2fredfunnymr
3jennymadmrs
4adamsillymr
5fredfunnymr
6jennymadmrs
So there we go, duplicating your database is actually incredibly easy! 🙂

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 Williams

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

Post Tags:
, , , , ,
  • Kenoli

    Here's a way to do it with much less typing:

    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.

    With line 2 & 4 omitted, this is a short 2 line solution.

    --Kenoli

  • Thanks for your solution I will update my post and provide your solution with credit.