DISQUS

Tech-Recipes: Copy an existing MySQL table to a new table | MySQL | Tech-Recipes

  • Thennarasu · 1 year ago
    It wont work without the keyword 'INTO'.

    CORRECT IT AS ,

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