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 newtable LIKE production.oldtable;
INSERT newtable SELECT * FROM production.oldtable;

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.

Thanks to Tech-Recipes Post.

About selvam4win

I am Selvam. I am very jolly person and frank one.

Posted on January 8, 2010, in MySql, Query and tagged , , . Bookmark the permalink. 1 Comment.

  1. Hi! I’ve been following your web site for a long time now and finally got the courage to go ahead and give you a shout out from Humble Texas! Just wanted to mention keep up the excellent work!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: