Copy an existing MySQL table to a new table
Posted by Quinn McHenry in MySQL
This is a great set of two commands that allow the creation and population of a new table with the structure and data of an existing table. This provides a quick means of making a point-in-time copy of a table and is a safe, easy way to make a quick copy of a table for testing an application in development on live data without risking a production environment.
To make a copy of the table recipes which is in a different database called production into a new table called recipes_new in the currently selected database, use these two commands:
CREATE TABLE recipes_new LIKE production.recipes;
INSERT recipes_new SELECT * FROM production.recipes;
The first command creates the new table recipes_new by duplicating the structure of the existing table. The second command copies the data from old to new.
The nomenclature production.recipes is a means of specifying the database and table in the same way that a file can be specified by its directory path. It is optional. If production was left off, MySQL would assume that the recipes table was also in the currently selected database.
About Quinn McHenry
View more articles by Quinn McHenry
The Conversation
Follow the reactions below and share your own thoughts.
November 01, 2008 at 9:02 am, Thennarasu said:
It wont work without the keyword ‘INTO’.
CORRECT IT AS ,
INSERT INTO recipes_new SELECT * FROM production.recipes;
August 22, 2012 at 12:26 pm, nathj07 said:
> It does actually work with the INTO keyword – I just tried it and it works fine.
November 12, 2008 at 6:23 pm, Dan said:
Awesome, thanks!
November 12, 2008 at 7:11 pm, jaxfor3 said:
It worked without the ‘INTO’ for me.
December 11, 2008 at 12:59 pm, Alfredas said:
It’s working!!! Thank you very much!
April 12, 2009 at 8:18 pm, Chris said:
Very helpful! thank you
July 16, 2009 at 1:30 am, Brandon said:
Thanks a lot, this saved me a lot of time!
August 14, 2009 at 3:58 pm, Name said:
The simplest solutions are the best. Thanks!
August 17, 2009 at 8:55 am, Anonymous said:
This could also be done in one step with the following:
create table recipies_new select * from production.recipies;
Be aware that this method doesn’t seem to duplicate any of the indexes from the original table into the new table. [Using MySQL 5.1]
August 28, 2009 at 1:08 pm, Anonymous said:
I was just looking for this!
thanks
September 01, 2009 at 9:42 pm, viralpatel said:
It creates a duplicate table but does not copy the constraints and other meta details of a table
September 08, 2009 at 4:16 am, Anonymous said:
yeah the single-line command is a bad scene. Even the two-line command will have issues in some cases, eg if you have foreign key restraints or are inserting rows with autoincrement keys that equal 0 (they get incremented to 1). I wrapped the statements in code used by mysqldump.
Also doing a mysqldump, replacing the table names in the statements, and then loading the resulting dump is another way to do this. It’s actually probably more robust than doing it with insert..select, but will be slower and a bit tricky.
August 22, 2012 at 12:27 pm, nathj07 said:
> Yeah, but this can be useful for a simple back up of just the data
March 02, 2010 at 2:50 am, Cheap said:
Nice, it works
March 15, 2010 at 9:55 am, Anonymous said:
thanks ..
March 25, 2010 at 10:25 am, chandan said:
hey it worked for me
thanks
March 29, 2010 at 3:13 am, coco said:
Thank you.
This helped me a lot
April 13, 2010 at 10:03 pm, Better MySQL said:
Doesn’t work on a MySQL 4.x server. Might work on MySQL 5.x. This will also supposedly not copy indexes or foreign key constraints. Makes this approach fairly limited.
What most people don’t realize is that MySQL stores each table in its own set of flat files. MyISAM, for instance, creates tablename.MYD, tablename.MYI, and tablename.frm files. D = Data, I = Index(es), frm = structure. I’ve found you can clone a table VERY quickly – even on a heavily-used live production system with millions of rows – by using the file system to copy these files to new names and then running the “FLUSH TABLES”, “FLUSH QUERY CACHE”, and “RESET QUERY CACHE” MySQL commands. Although, it is best to temporarily take the database offline before cloning, it can be done on a live system. If the destination table/index gets corrupted during the copy, a “REPAIR TABLE” command usually fixes the problem (or re-running the whole process).
April 20, 2010 at 7:44 am, Anonymous said:
simply superb……………
July 12, 2010 at 9:58 pm, jtes0111 said:
On 5.1 this method copied all my indexes between tables (they were MyISAM ones).
Also, it can all be done in one command:
CREATE TABLE recipes_new SELECT * FROM production.recipes;
as explained in the MySQL help – http://dev.mysql.com/doc/refman/5.1/en/create-table.html
July 24, 2010 at 1:56 am, Rajeevmcadeveloper said:
create table raj as
select * from employees where 1=0;
November 07, 2010 at 3:01 am, nana juhana said:
How to copy table to different host ? please !!
December 30, 2010 at 9:16 am, Jdssarmiento said:
Thanks so much!
February 10, 2011 at 5:20 pm, Adrian Emmott said:
You are my hero
March 07, 2011 at 10:51 am, singletony said:
Thanx, it definitely works.
However, I tried to do this on a 15 million record table,
The INSERT INTO statement, being a logged operation, combined with the possibility for the existence of indexes, possibly even clustered ones, makes this an all around inapplicable solution.
Can you think of any alternative.
March 23, 2011 at 6:27 am, Amilaudana said:
perfect. Thanks.
April 22, 2011 at 5:01 pm, Oscar said:
Great! I am a beginner of mysql. That’s is very useful for me. Thank you very much.
June 05, 2011 at 12:18 pm, Yunususmani said:
Its working dear…………..
June 06, 2011 at 5:38 am, Gopinathkarangula said:
HI to all, i agree with all the above possibilities to copy the table, with data, and structure, but all the above queries wont copy the key constraints ( relations). Is there any query that copy index, key fields in mysql
July 14, 2011 at 4:39 am, Ranggi Putra said:
Outstanding…
January 24, 2012 at 11:55 pm, arpan said:
Can somebody plz tell me how to copy a table partially (selected fields) to a new/existing table’s corresponding fields! I’m supposed to do it using mysql(only, not using any programming language.)
thanks and regards.
February 08, 2012 at 6:28 am, arun said:
U can use the limit keyword in mysql
select * from tablename limit 100
selects 100 rows from the table
April 28, 2012 at 9:20 am, jsding said:
It will not copy any keys(primary, unique index etc.) of the table.
show create table production.recipes; and execute the resultset; then insert into …
DbFacePHP for MySQL
May 20, 2012 at 3:47 pm, Gagandeep Singh said:
Woohoo!!! cloned my favorite table..
June 08, 2012 at 12:38 pm, bilietai said:
eport structure, execute sql with renamed table, use insert sql to import data, if u want to have triggers etc
June 15, 2012 at 1:41 am, amar said:
I installed mysql 5.1. I want to create a table.
I tried. but it is not working for me. plz tell me how to clear this problem
mysql> CREATE TABLE stable (sname varchar(30), id int)
->
->
->
mysql> CREATE TABLE stable (sname varchar(30), id int);
ERROR 1046 (3D000): No database selected
June 26, 2012 at 10:29 pm, Thomas said:
Very powerful MySql commands, saved me a bunch of time thanks!
June 30, 2012 at 9:25 pm, Joe Bowman said:
Very useful; previously had the create table syntax stored as a string in my program that was executed when a new table was required.
This was okay to start with, but:
1) When table layout is altered, code needs to be changed
2) When table layout is complex, create table syntax is huge
I now just use the single line ‘CREATE x LIKE y’ and is works perfect. Thanks!
October 15, 2012 at 11:15 am, Marcelo said:
Can´t see that working with two different instances of mysql or even to another db source…
How can we copy the contents from one database to another?
November 07, 2012 at 4:48 pm, Yves said:
Thanks. Time saver!
Using 5.5, and the 2 commands method described does copy indexes and primary key.
November 19, 2012 at 10:01 am, ali saebifar said:
nice tip
December 18, 2012 at 3:47 am, ANVAR SADIQ P said:
thanks
December 27, 2012 at 10:53 pm, Su said:
You’re the best!
January 11, 2013 at 7:42 am, alenn said:
thanks for sharing, without “into” it works for me too.
January 19, 2013 at 8:34 pm, bi said:
thx!
February 27, 2013 at 6:16 am, Desert Fox said:
that help me
March 11, 2013 at 7:34 am, pom said:
Sadly, it doesn’t work if production.recipes has a foreign key field.
Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`mydatabase`.`mytable`, CONSTRAINT `inter_id_refs_id_656ae4d26b451625` FOREIGN KEY (`inter_id`) REFERENCES `myothertable` (`id`)) 0.047 sec
March 18, 2013 at 11:01 pm, nano said:
Thanks.
March 18, 2013 at 11:02 pm, nano said:
Very Helpful. Thanks a lot.