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.

  • Thennarasu

    It wont work without the keyword ‘INTO’.

    CORRECT IT AS ,

    INSERT INTO recipes_new SELECT * FROM production.recipes;

    • nathj07

      > It does actually work with the INTO keyword – I just tried it and it works fine.

      • saidarao

        Super …Working.

  • Dan

    Awesome, thanks!

  • http://stephenjacksonmakeslovetopressure.com jaxfor3

    It worked without the ‘INTO’ for me.

  • Alfredas

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

  • Chris

    Very helpful! thank you :)

    • jegan

      nice awesome by jegan

  • Brandon

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

  • Name

    The simplest solutions are the best. Thanks!

  • Anonymous

    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

    I was just looking for this!
    thanks

  • http://viralpatel.net/blogs/ viralpatel

    It creates a duplicate table but does not copy the constraints and other meta details of a table

    • Anonymous

      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.

    • nathj07

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

  • http://www.cheap-car-insurance.biz Cheap

    Nice, it works

  • Anonymous

    thanks ..

  • http://www.chandan.in/ chandan

    hey it worked for me :) thanks

  • coco

    Thank you.
    This helped me a lot

  • Better MySQL

    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

    simply superb……………

  • jtes0111

    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

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

  • nana juhana

    How to copy table to different host ? please !!

  • Jdssarmiento

    Thanks so much!

  • Adrian Emmott

    You are my hero

  • singletony

    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

    perfect. Thanks.

  • Oscar

    Great! I am a beginner of mysql. That’s is very useful for me. Thank you very much.

  • Yunususmani

    Its working dear…………..

  • Gopinathkarangula

    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

    Outstanding…

  • http://facebook.com/username.public arpan

    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.

  • arun

    U can use the limit keyword in mysql

    select * from tablename limit 100

    selects 100 rows from the table

  • jsding

    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

  • Gagandeep Singh

    Woohoo!!! cloned my favorite table..

  • bilietai

    eport structure, execute sql with renamed table, use insert sql to import data, if u want to have triggers etc

  • amar

    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

  • Thomas

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

  • Joe Bowman

    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!

  • Marcelo

    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?

  • Yves

    Thanks. Time saver!
    Using 5.5, and the 2 commands method described does copy indexes and primary key.

  • ali saebifar

    nice tip

  • ANVAR SADIQ P

    thanks

  • Su

    You’re the best!

  • alenn

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

  • bi

    thx!

  • Desert Fox

    that help me :)

  • pom

    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

  • nano

    Thanks.

  • nano

    Very Helpful. Thanks a lot.

  • J

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

  • Norman

    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.

  • Sumit

    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?

  • vcamargo

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

  • hades

    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.

  • Tarun Nagpal

    Good code.

  • Junaid PV

    Thanks it is very helpful! Thanks.

  • Aditya

    Thank you very much it worked fine by adding INTO to the insert statement.
    Thanks for posting the query

  • http://sopost.ru/ frops

    Thank you!