Three easy ways to create a copy of MySQL table (example query included)

Since MySQL doesn’t have a copy table statement, you need to create a work-around query when you want to copy an existing MySQL database table as a new table.

Depending on how identical the copy table is to the original table you want to be, there are three easy ways you can copy a table:

• CREATE TABLE ... AS query to copy the table column attributes and data
• CREATE TABLE ... LIKE ... query to also copy the table indexes
• SHOW CREATE TABLE query to create an identical copy of the original table

This tutorial will show you how to use all the methods above to copy a MySQL table.

Copy table using CREATE TABLE AS query

If you want to create a shallow clone table that only has the original table column attributes and data, you can use the CREATE TABLE ... AS ... query.

For example, suppose you have a table named customer and you need to create a copy of it called customer_clone table.

This is how you do it:

CREATE TABLE customer_clone AS SELECT * FROM customer;

The command above will save the result of the SELECT statement as a new table in your MySQL database.

Keep in mind that the method above doesn’t copy the original table’s indexes, triggers, or constraints because it merely saves the result of the query as a new table.

If you want to copy the table’s indexes as well, you need to use the CREATE TABLE ... LIKE ... query

Copy table using CREATE TABLE LIKE query

To copy an existing table as a new table, you can use the CREATE TABLE ... LIKE ... query and pass the name of the original table next to the LIKE clause.

For example, suppose you have a table named customer, then here’s how to create a copy of that table:

CREATE TABLE copy_customer LIKE customer;

A new empty table named copy_customer will be created in your MySQL database that will be a duplicate of the customer table.

Keep in mind that copying a MySQL table this way will copy the original table structure and indexes, but any triggers and constraints placed on the original table won’t be copied into the new table.

This is because table constraint names must be unique within a single database, so you need to add the constraints manually with a different name.

After creating the table, you can insert data from the original table into the copy table using an INSERT statement as follows:

INSERT INTO [destination table] SELECT * FROM [source table]

The SQL query below will copy all data from the customer table to the copy_customer table:

INSERT INTO copy_customer SELECT * FROM customer;

Now you’ve learned how to copy a table using CREATE TABLE ... LIKE ... query. Let’s learn how you can also copy a table using the SHOW CREATE TABLE query.

Copy table using SHOW CREATE TABLE query

The SHOW CREATE TABLE query is used to display the SQL query to create a specific table.

For example, to show the CREATE TABLE query for the customer table, you can use the following SQL query:

SHOW CREATE TABLE customer\G

In my database, the following result set is displayed:

*************************** 1. row ***************************
Table: customer
Create Table: CREATE TABLE `customer` (
  `customer_id` smallint unsigned NOT NULL AUTO_INCREMENT,
  `store_id` tinyint unsigned NOT NULL,
  `first_name` varchar(45) NOT NULL,
  `last_name` varchar(45) NOT NULL,
  `email` varchar(50) DEFAULT NULL,
  `address_id` smallint unsigned NOT NULL,
  `active` tinyint(1) NOT NULL DEFAULT '1',
  `create_date` datetime NOT NULL,
  `last_update` timestamp NULL 
    DEFAULT CURRENT_TIMESTAMP 
    ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`customer_id`),
  KEY `idx_fk_store_id` (`store_id`),
  KEY `idx_fk_address_id` (`address_id`),
  KEY `idx_last_name` (`last_name`),
  CONSTRAINT `fk_customer_address` FOREIGN KEY (`address_id`) 
    REFERENCES `address` (`address_id`) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `fk_customer_store` FOREIGN KEY (`store_id`) 
    REFERENCES `store` (`store_id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=600 DEFAULT CHARSET=utf8mb4 
COLLATE=utf8mb4_0900_ai_ci

The whole query for creating the table structure will be displayed as a result set of the SHOW CREATE TABLE query.

Now you can copy the CREATE TABLE statement and change the name of the table to create a copy of the original table.

Let’s change the name from customer to customer_copy:

CREATE TABLE `customer_copy` (

If the CREATE TABLE statement has CONSTRAINT clauses, you need to change the name of those CONSTRAINT clauses because MySQL needs constraint names to be unique.

The customer table above has two constraint clauses as follows:

CONSTRAINT `fk_customer_address` FOREIGN KEY (`address_id`) 
  REFERENCES `address` (`address_id`) ON DELETE RESTRICT ON UPDATE CASCADE,
CONSTRAINT `fk_customer_store` FOREIGN KEY (`store_id`) 
  REFERENCES `store` (`store_id`) ON DELETE RESTRICT ON UPDATE CASCADE

Without changing the constraint names, then you will have a Duplicate foreign key constraint name error:

Duplicate foreign key constraint name fk_customer_address

To avoid the error, you need to change the fk_customer_address and fk_customer_store names to something else.

For our example, we can use fk_customer_copy_address and fk_customer_copy_store for the constraint names.

Now you should be able to create a copy of the customer table. If you want to copy the table data too, then you can use the INSERT ... SELECT * statement as in the first method:

INSERT INTO customer_copy SELECT * FROM customer;

You can also include a WHERE CLAUSE to the SELECT query to copy only partial data that meets certain conditions.

Copy table into a different database

When you need to copy a table into a different database, you can add the source and target database names to the CREATE TABLE queries.

Here are some example queries for copying an existing table into a different database:

CREATE TABLE target_database.customer_clone 
  LIKE source_database.customer;

  -- or

CREATE TABLE target_database.customer_clone 
  AS SELECT * FROM source_database.customer;

  -- or
CREATE TABLE target_database.customer (
  -- column attributes omitted for brevity
)

And that’s how you copy one database table to another database.

Conclusion

Since there’s no MySQL copy table syntax, you need to make use of the existing MySQL features to copy an existing table into a new table.

The three methods mentioned above should be enough to help you copy an existing table as a new table.

If you only need to create a shallow copy with the same column attributes and data, you can use the CREATE TABLE ... AS ... query.

If you also need to copy related triggers, indexes, and constraints, then you can use either the CREATE TABLE ... LIKE ... or SHOW CREATE TABLE query.

Feel free to copy and modify the example queries above for your project 😉