How to Duplicate a Table in MySQL

When working with databases, we may encounter a situation where we have to copy all the data from one table to another. To accomplish this, we can follow multiple steps, with each of them having their own pros and cons.

Today we will look into the various ways we can copy a table in MySQL.

Copy table structure and data

We can copy the table structure and data using the CREATE TABLE ... AS SELECT command in MySQL. The only drawback of this command is that it does not copy keys and indices from the old table to the new table.

To copy table to the same database:

CREATE TABLE new_table_name

AS

SELECT * FROM old_table_name;

Here, the structure and data of the old table will be copied to the new table in the same database.

To copy table to another database:

CREATE TABLE database2.new_table_name

AS

SELECT * FROM database1.old_table_name;

Here, the structure and data of the old table from database1 will be copied to the new table in database2.

Copy table structure only

As we have already seen that the above command while copying the table structure and data in a single go does not copy the keys and indices to the new table, to overcome this we can use another command CREATE TABLE... LIKE, this command only copies the structure of the table, but it copies the complete structure including the keys and the indices.

To copy table to the same database:

CREATE TABLE new_table_name

LIKE old_table_name;

Here, the structure and data of the old table will be copied to the new table in the same database.

To copy table to another database:

CREATE TABLE database2.new_table_name

LIKE database1.old_table_name;

Here, the structure and data of the old table from database1 will be copied to the new table in database2. But here this may cause issues, as copying the complete structure with keys and indices may not work due to the corresponding key tables not being there. To overcome this, we can use the command from the previous section but with limit 0 ensuring that only the structure is copied and not the data.

CREATE TABLE database2.new_table_name

AS

SELECT * FROM database1.old_table_name LIMIT 0;

Copy data from one table to another

In the previous section, we copied the structure from one table to another. Now the next step is to copy the data as well, for this, we will be using the INSERT command as follows:

INSERT new_table_name SELECT * FROM old_table_name;

Using this, you can copy data to new tables easily to the same database or another database.If you have any queries, please let us know in the comment section below.

Thank You