Copy an existing MySQL table to a new table

Contributor Icon Contributed by qmchenry Date Icon July 4, 2006  
Tag Icon 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.

Previous recipe | Next recipe |
 
  • Thennarasu
    It wont work without the keyword 'INTO'.

    CORRECT IT AS ,

    INSERT INTO recipes_new SELECT * FROM production.recipes;
  • Dan
    Awesome, thanks!
  • It worked without the 'INTO' for me.
  • Alfredas
    It's working!!! Thank you very much! :-)
  • Chris
    Very helpful! thank you :)
  • Brandon
    Thanks a lot, this saved me a lot of time!
  • Name
    The simplest solutions are the best. Thanks!
  • bryonbean
    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]
  • soodvarun
    I was just looking for this!
    thanks
  • It creates a duplicate table but does not copy the constraints and other meta details of a table
  • intuited
    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.
blog comments powered by Disqus