Copy a MySQL table including indexes

I can copy a MySQL table to create a new table:

CREATE TABLE newtable SELECT * FROM oldtable

This works, but the indexes are not copied to the new table. How can I copy a table including the indexes?

 Answers

CREATE TABLE newtable LIKE oldtable; 
INSERT newtable SELECT * FROM oldtable;

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.

Copy a MySQL table with phpMyAdmin

Last week I looked at how to copy a table with MySQL using some SQL queries and then on Sunday a PHP script to automate the process. This post looks at how to copy a table with phpMyAdmin so you can easily do the same thing using a webpage GUI.

Log into phpMyAdmin, select the database and then the table that you wish to copy.

Click the "Operations" tab which appears in the tabs near the top of the page in the right frame. This tab is highlighted in the screenshot below with the red arrow pointing to it.

The available operations appear underneath the tabs and there are various options. The one we are interested in with this post is the "Copy table to..." section which is highlighted with the red box.

You can either make a copy of the table to the current database, which is selected by default from the database drop down box, or copy it to another database for which you have the appropriate permissions.

After the drop down box is a dot ( . ) and then a textbox where you enter the name of the table to copy the structure and / or data into.

There are then three radio options:

• structure only: this will create a copy of the table with the name you specify but won't copy any data over
• structure and data: this will do the above and then copy all data across using INSERT INTO ... SELECT * FROM ...
• data only: this only does the INSERT INTO ... SELECT * FROM ... query

You can also select whether to drop the table that you are copying to before creating the table and copying the data. This runs a DROP TABLE IF EXISTS query for the copy of the table, not the original. It has no effect if selected when doing a data only copy.

Another option is to copy the auto increment value. By default this is not done and the auto increment value will end up being whatever it would be after the INSERT INTO query is done. If checked then the CREATE TABLE syntax will include the auto increment value from the original table.

The final checkbox is to change to the copied table after the queries are done. If unchecked the resulting page will still be looking at the original table; if checked it will change to the copied database (if applicable) and table.

Related posts:

• Show indexes for a table with MySQL
• PHP script to make a backup copy of a MySQL table
• Copy a table in MySQL with CREATE TABLE LIKE

Copy a table in MySQL

Sometimes you might need to quickly and easily create a backup copy of a table in MySQL before doing some work to the existing table. That way you can easily copy the data back from the backup table into the original table if something goes wrong. This post looks at how to create a copy of a table in MySQL and then copy the data from the original table into the copy.

Please note

This post has been revised and republished as Copy a table in MySQL with CREATE TABLE LIKE to use a simpler method of creating the new table.

Create a copy of the table

MySQL has a SQL query "SHOW CREATE TABLE" which shows you the SQL required to create a table. If we have an example table called "products" you would issue the following SQL command to get the SQL to create the table:

SHOW CREATE TABLE products

If you're running this from the MySQL command line client you'll get a result like this:

+----------+-------------------------------------------------+
| Table    | Create Table                                    |
+----------+-------------------------------------------------+
| products | CREATE TABLE `products` (
  `product_id` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(50) NOT NULL,
  `description` varchar(255) NOT NULL,
  `price` decimal(10,2) NOT NULL,
  PRIMARY KEY  (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+----------+-------------------------------------------------+
1 row in set (0.00 sec)

I've highlighted in red the part we want to copy and use as the SQL to execute.

So you'd copy the SQL needed to create the table and change the `products` to e.g. `products_bak` and then execute a new query to create the copy of the table:

CREATE TABLE `products_bak` (
  `product_id` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(50) NOT NULL,
  `description` varchar(255) NOT NULL,
  `price` decimal(10,2) NOT NULL,
  PRIMARY KEY  (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Copying the data from the original table to the new table

Now that we've created the backup table it's simply a matter of running a INSERT INTO ... SELECT query to copy the data from the original table into the copy:

INSERT INTO products_bak SELECT * FROM products

Copying the data back again

If something went wrong with your original table after you've been mucking around with it, you can then simply delete the data from the original table using TRUNCATE and then use the same query above to copy it back again:

TRUNCATE products;
INSERT INTO products SELECT * FROM products_bak

Note that depending on the size of your table this may take some time, and it's not recommended to do it on a production website.

Future posts

On Sunday I'll post a PHP script which automates this process, and next week's MySQL post will show how to do the same thing in phpMyAdmin which means you don't need to type any SQL or run any special scripts.

Make sure to subscribe to my RSS feed (see below) so you don't miss out!

Related posts:

• Show indexes for a table with MySQL
• Copy a MySQL table with phpMyAdmin
• Using SELECT REPLACE with MySQL
• Insert multiple records into MySQL with a single query
• Check if a MySQL table exists
• Using the MySQL command line tool

Copy a table in MySQL with CREATE TABLE LIKE

I wrote a post about how to copy a table in MySQL using the SHOW CREATE TABLE sql query. There's a much easier way to do this using CREATE TABLE LIKE, a function which was depths of my brain somewhere but I'd since forgotten. This post revises and republishes my previous post using this simpler process.

Create a copy of the table

To create a copy of a table called e.g. products and copy the data from this table into the copy the table must first be created. If we wanted to create a copy called e.g. products_bak we can use the CREATE TABLE LIKE query like so:

CREATE TABLE products_bak LIKE products

An empty copy of the table is then created. Note that if you have an auto-incremental primary key that the next value if reset to the default, usually 1.

Copying the data from the original table to the new table

Now that we've created the backup table it's simply a matter of running a INSERT INTO ... SELECT query to copy the data from the original table into the copy:

INSERT INTO products_bak SELECT * FROM products

Copying the data back again

If something went wrong with your original table after you've been mucking around with it, you can then simply delete the data from the original table using TRUNCATE and then use the same query above to copy it back again:

TRUNCATE products;
INSERT INTO products SELECT * FROM products_bak

Note that depending on the size of your table this may take some time, and it's not recommended to do it on a production website.

Future posts

Tomorrow's post will feature a PHP script to automate the process and on Wednesday I'll show how to copy a table using phpMyAdmin so you can use a GUI instead of running SQL queries.
 

Related posts:

• Show indexes for a table with MySQL
• Copy a MySQL table with phpMyAdmin
• Using SELECT REPLACE with MySQL
• Insert multiple records into MySQL with a single query
• Check if a MySQL table exists
• Using the MySQL command line tool

Mysql: Copy data from one tabe to another (update existed records)

Example shows how to update records in one table with data from another table.

CREATE TABLE table_a (
  id INT(11) DEFAULT NULL,
  column_a INT(11) DEFAULT NULL
);
CREATE TABLE table_b (
  id INT(11) DEFAULT NULL,
  column_b INT(11) DEFAULT NULL
);
INSERT INTO table_a VALUES 
  (1, 10),
  (2, 20),
  (3, 30),
  (4, NULL),
  (5, 0);
INSERT INTO table_b VALUES 
  (1, 1),
  (2, NULL),
  (3, 0),
  (4, 4),
  (5, 5);

Update data:

UPDATE table_a a
  JOIN table_b b
    ON a.id = b.id
SET
  b.column_b = a.column_a;

Result table:

SELECT * FROM table_b;
+------+----------+
| id   | column_b |
+------+----------+
|    1 |       10 |
|    2 |       20 |
|    3 |       30 |
|    4 |     NULL |
|    5 |        0 |
+------+----------+

Mysql: Copy data from one tabe to a new table

Shows how to create new the same table and copy data.

CREATE TABLE table_a (
  id INT(11) DEFAULT NULL,
  column_a INT(11) DEFAULT NULL
);
INSERT INTO table_a VALUES 
  (1, 10),
  (2, 20),
  (3, 30),
  (4, NULL),
  (5, 0);

Create and copy data:

CREATE TABLE table_b SELECT * FROM table_a;

Check results:

SHOW CREATE TABLE table_b;
>
CREATE TABLE `table_b` (
  `id` int(11) DEFAULT NULL,
  `column_a` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1

SELECT * FROM table_b;
+------+----------+
| id   | column_a |
+------+----------+
|    1 |       10 |
|    2 |       20 |
|    3 |       30 |
|    4 |     NULL |
|    5 |        0 |
+------+----------+

Mysql: Copy data from one tabe to another

Create and populate tables:

CREATE TABLE table_a (
  id INT(11) DEFAULT NULL,
  column_a INT(11) DEFAULT NULL
);
CREATE TABLE table_b (
  id INT(11) DEFAULT NULL,
  column_b INT(11) DEFAULT NULL
);
INSERT INTO table_a VALUES 
  (1, 10),
  (2, 20),
  (3, 30),
  (4, NULL),
  (5, 0);

Copy data into second table:

INSERT INTO table_b SELECT id, column_a FROM table_a;

Check results:

SELECT * FROM table_b;
+------+----------+
| id   | column_a |
+------+----------+
|    1 |       10 |
|    2 |       20 |
|    3 |       30 |
|    4 |     NULL |
|    5 |        0 |