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;
INSERT recipes_new SELECT * FROM;

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


    INSERT INTO recipes_new SELECT * FROM;


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


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

        Super …Working.


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

    Awesome, thanks!


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

    It worked without the ‘INTO’ for me.


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

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


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

    Very helpful! thank you :)


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

      nice awesome by jegan


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

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


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

    The simplest solutions are the best. Thanks!


  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]


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

    I was just looking for this!


  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


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


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

    Nice, it works


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

    thanks ..


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

    hey it worked for me :) thanks


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

    Thank you.
    This helped me a lot


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


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

    simply superb……………


  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;

    as explained in the MySQL help –


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

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


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

    How to copy table to different host ? please !!


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

    Thanks so much!


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

    You are my hero


  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.


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

    perfect. Thanks.


  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.


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

    Its working dear…………..


  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


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



  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.


  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


  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; and execute the resultset; then insert into …

    DbFacePHP for MySQL


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

    Woohoo!!! cloned my favorite table..


  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


  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


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

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


  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!


  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?


  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.


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

    nice tip


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



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

    You’re the best!


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

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


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



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

    that help me :)


  44. March 11, 2013 at 7:34 am, pom said:

    Sadly, it doesn’t work if 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


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



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

    Very Helpful. Thanks a lot.


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

    but i have something new:


  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.


  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?


  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! (:


  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.


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

    Good code.


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

    Thanks it is very helpful! Thanks.


Leave a Reply