Copy an existing MySQL table to a new table

Home -> Database -> MySQL

24902 views

From the computer of: qmchenry (339 recipes)
Created: Jul 04, 2006


Add a comment

Add to:
Add to stumbleuponAdd to del.icio.usDigg itAdd to FURL

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.

Subscribe to the Tech-Recipes Newsletter

You can get tips like this delivered in your email every week!

Enter your Email

We will never, ever sell your email address or spam you.





Related recipes:

  Rename or change name of MySQL table
  Add a column to an existing MySQL table
  Delete a column from an existing MySQL table
  Describe the column structure of a MySQL table
  Drop or delete a table in MySQL
  Modify an existing MySQL column
  Create a basic MySQL table
  MySQL: Increment an exisitng value
  Create a MySQL table with a primary key
  Import CSV file directly into MySQL

 

Sponsored links

 

Affiliated Sites


Certification Journals
Free certification exam study guides and help

Tech-Recipes Blogs
More tips and news from the tech-recipes authors

 

Login

Nickname

Password

Don't have an account yet? You can create one. As a registered user you have some advantages like theme manager, comments configuration and post comments with your name.