Copy an existing MySQL table to a new table
Contributed by qmchenry 
Tagged: 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.
You may also like -
Oracle: How to Create a Copy of Table Data
Rename or change name of MySQL table
Drop or delete a table in MySQL
Delete a column from an existing MySQL table
Add a column to an existing MySQL table
MySQL: Use CONCAT to include text in SELECT results
Access: Change the start value of an autonumber field
Create a basic MySQL table
Thennarasu said on November 1, 2008
It wont work without the keyword ‘INTO’.
CORRECT IT AS ,
INSERT INTO recipes_new SELECT * FROM production.recipes;
Dan said on November 12, 2008
Awesome, thanks!
jaxfor3 said on November 12, 2008
It worked without the ‘INTO’ for me.
Alfredas said on December 11, 2008
It’s working!!! Thank you very much! :-)
Chris said on April 12, 2009
Very helpful! thank you :)
Brandon said on July 16, 2009
Thanks a lot, this saved me a lot of time!
Name said on August 14, 2009
The simplest solutions are the best. Thanks!
Anonymous said on August 17, 2009
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]
Anonymous said on August 28, 2009
I was just looking for this!
thanks
viralpatel said on September 1, 2009
It creates a duplicate table but does not copy the constraints and other meta details of a table
Anonymous said on September 8, 2009
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.
Cheap said on March 2, 2010
Nice, it works
Anonymous said on March 15, 2010
thanks ..
chandan said on March 25, 2010
hey it worked for me :) thanks
coco said on March 29, 2010
Thank you.
This helped me a lot
Better MySQL said on April 13, 2010
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).
Anonymous said on April 20, 2010
simply superb……………
jtes0111 said on July 12, 2010
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
Rajeevmcadeveloper said on July 24, 2010
create table raj as
select * from employees where 1=0;
nana juhana said on November 7, 2010
How to copy table to different host ? please !!
Jdssarmiento said on December 30, 2010
Thanks so much!
Adrian Emmott said on February 10, 2011
You are my hero
singletony said on March 7, 2011
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.
Amilaudana said on March 23, 2011
perfect. Thanks.
Oscar said on April 22, 2011
Great! I am a beginner of mysql. That’s is very useful for me. Thank you very much.
Yunususmani said on June 5, 2011
Its working dear…………..
Gopinathkarangula said on June 6, 2011
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
Ranggi Putra said on July 14, 2011
Outstanding…
arpan said on January 24, 2012
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.