Copy an existing MySQL table to a new table

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

Quinn was one of the original co-founders of Tech-Recipes. He is currently crafting iOS applications as a senior developer at Small Planet Digital in Brooklyn, New York.
View more articles by Quinn McHenry

The Conversation

Follow the reactions below and share your own thoughts.

58 Responses to “Copy an existing MySQL table to a new table”

  1. 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;

    Reply

    • 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.

      Reply

      • August 05, 2013 at 5:03 am, saidarao said:

        Super …Working.

        Reply

  2. November 12, 2008 at 6:23 pm, Dan said:

    Awesome, thanks!

    Reply

  3. November 12, 2008 at 7:11 pm, jaxfor3 said:

    It worked without the ‘INTO’ for me.

    Reply

  4. December 11, 2008 at 12:59 pm, Alfredas said:

    It’s working!!! Thank you very much! :-)

    Reply

  5. April 12, 2009 at 8:18 pm, Chris said:

    Very helpful! thank you :)

    Reply

    • November 08, 2013 at 9:51 am, jegan said:

      nice awesome by jegan

      Reply

  6. July 16, 2009 at 1:30 am, Brandon said:

    Thanks a lot, this saved me a lot of time!

    Reply

  7. August 14, 2009 at 3:58 pm, Name said:

    The simplest solutions are the best. Thanks!

    Reply

  8. 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]

    Reply

  9. August 28, 2009 at 1:08 pm, Anonymous said:

    I was just looking for this!
    thanks

    Reply

  10. 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

    Reply

    • 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.

      Reply

    • August 22, 2012 at 12:27 pm, nathj07 said:

      > Yeah, but this can be useful for a simple back up of just the data

      Reply

  11. March 02, 2010 at 2:50 am, Cheap said:

    Nice, it works

    Reply

  12. March 15, 2010 at 9:55 am, Anonymous said:

    thanks ..

    Reply

  13. March 25, 2010 at 10:25 am, chandan said:

    hey it worked for me :) thanks

    Reply

  14. March 29, 2010 at 3:13 am, coco said:

    Thank you.
    This helped me a lot

    Reply

  15. 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).

    Reply

  16. April 20, 2010 at 7:44 am, Anonymous said:

    simply superb……………

    Reply

  17. 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

    Reply

  18. July 24, 2010 at 1:56 am, Rajeevmcadeveloper said:

    create table raj as
    select * from employees where 1=0;

    Reply

  19. November 07, 2010 at 3:01 am, nana juhana said:

    How to copy table to different host ? please !!

    Reply

  20. December 30, 2010 at 9:16 am, Jdssarmiento said:

    Thanks so much!

    Reply

  21. February 10, 2011 at 5:20 pm, Adrian Emmott said:

    You are my hero

    Reply

  22. 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.

    Reply

  23. March 23, 2011 at 6:27 am, Amilaudana said:

    perfect. Thanks.

    Reply

  24. 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.

    Reply

  25. June 05, 2011 at 12:18 pm, Yunususmani said:

    Its working dear…………..

    Reply

  26. 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

    Reply

  27. July 14, 2011 at 4:39 am, Ranggi Putra said:

    Outstanding…

    Reply

  28. 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.

    Reply

  29. 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

    Reply

  30. 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

    Reply

  31. May 20, 2012 at 3:47 pm, Gagandeep Singh said:

    Woohoo!!! cloned my favorite table..

    Reply

  32. 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

    Reply

  33. 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

    Reply

  34. June 26, 2012 at 10:29 pm, Thomas said:

    Very powerful MySql commands, saved me a bunch of time thanks!

    Reply

  35. 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!

    Reply

  36. 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?

    Reply

  37. 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.

    Reply

  38. November 19, 2012 at 10:01 am, ali saebifar said:

    nice tip

    Reply

  39. December 18, 2012 at 3:47 am, ANVAR SADIQ P said:

    thanks

    Reply

  40. December 27, 2012 at 10:53 pm, Su said:

    You’re the best!

    Reply

  41. January 11, 2013 at 7:42 am, alenn said:

    thanks for sharing, without “into” it works for me too.

    Reply

  42. January 19, 2013 at 8:34 pm, bi said:

    thx!

    Reply

  43. February 27, 2013 at 6:16 am, Desert Fox said:

    that help me :)

    Reply

  44. 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

    Reply

  45. March 18, 2013 at 11:01 pm, nano said:

    Thanks.

    Reply

  46. March 18, 2013 at 11:02 pm, nano said:

    Very Helpful. Thanks a lot.

    Reply

  47. September 17, 2013 at 1:57 pm, J said:

    but i have something new:
    ——————————-
    CREATE TABLE NEW_TABLE AS SELECT * FROM OLD_TABLE;
    ——————————-
    WILL COPY ALL DATA FROM OLD TO NEW TABLE.

    Reply

  48. September 18, 2013 at 7:50 am, Norman said:

    Worked like a charm. thanks for sharing! I used it to update my user field to email addresses when I wanted the users to be able to login with their email addresses.

    Reply

  49. October 13, 2013 at 3:03 am, Sumit said:

    With the first query, I am getting the same table definition but without the constraints from the original. How to get that i.e exact table definition?

    Reply

  50. November 03, 2013 at 12:03 pm, vcamargo said:

    That’s was exactly what I was looking for! It worked perfectly, thanks a lot! (:

    Reply

  51. November 25, 2013 at 5:09 pm, hades said:

    if it will , or will not work with ‘INTO’ keyword depends on MySQL version – check the manual for reference.
    eitherway – using ‘INTO’ is safer, because it’s not deprecated and it runs through all MySQL versions.

    Reply

  52. December 27, 2013 at 1:30 am, Tarun Nagpal said:

    Good code.

    Reply

  53. March 31, 2014 at 1:58 am, Junaid PV said:

    Thanks it is very helpful! Thanks.

    Reply

Leave a Reply