Copy a table in MySQL with CREATE TABLE LIKE

Posted February 14th, 2009 in MySql

On Wednesday I wrote a post about how to copy a table in MySQL using the SHOW CREATE TABLE sql query. Since then Paul Williams in the UK emailed me to let me know that 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.