Cloning Table in MySQL

Duplicating a table in MySQL involves creating a new table that replicates an existing table's structure and/or data. This capability is valuable for creating backups, setting up test environments, or transferring data between databases.

In this article, we will explore various methods for duplicating tables in MySQL, including copying only the table structure, copying both structure and data and transferring tables across databases.

How to Duplicate a Table in MySQL

• Duplicating a table in MySQL involves creating a new table that mirrors an existing table's structure and/or data.
  
• This process can be useful for creating backups, setting up test environments or transferring data between databases.
  
• Below are the methods to duplicate a table in MySQL, covering copying table structure only, copying both structure and data and transferring tables between databases.

MуSQL copy table structure only

To copy only the structure of a table without its data, use the following command:

CREATE TABLE new_table LIKE original_table;

Verification:

After executing the command, we can verify the creation of the new table by listing the tables in the database:

SHOW TABLES;

Verification Output:

Example:

CREATE TABLE employees_clone LIKE employees;

This command creates a new table employees_clone with the same columns and data types as the employees table.

Now we will Copy Table Data to Another Table

To copy both the structure and the data from one table to another, follow these commands:

CREATE TABLE new_table LIKE original_table;
INSERT INTO new_table SELECT * FROM original_table;

Verification:

You can verify the data copy by selecting data from the new table:

SELECT * FROM new_table;

Example:

CREATE TABLE employees_clone LIKE employees;
INSERT INTO employees_clone SELECT * FROM employees;

Verification Output:

Copy a Table from One Database to Another

If you need to clone a table from one database to another, you can follow these steps:

Step 1: Create the Table in the Target Database

CREATE TABLE target_db.new_table LIKE source_db.original_table;

Step 2: Copy Data to the New Table:

INSERT INTO target_db.new_table SELECT * FROM source_db.original_table;

Verification:

Switch to the target database and check the new table:

USE target_db;
SELECT * FROM new_table;

Example:

CREATE TABLE target_db.employees_clone LIKE source_db.employees;
INSERT INTO target_db.employees_clone SELECT * FROM source_db.employees;

Verification Output:

Example of Copy a Table to a New Table

To illustrate cloning a table with a specific example, let’s say you want to copy the sales_data table to a new table sales_data_archive.

Commands:

CREATE TABLE sales_data_archive LIKE sales_data;
INSERT INTO sales_data_archive SELECT * FROM sales_data;

Verification:

Check the contents of the new table:

SELECT * FROM sales_data_archive;

Example Data in sales_data:

Verification Output:

Conclusion

Cloning tables in MySQL is a fundamental skill for efficient database management. By understanding and applying the methods to duplicate table structures and data, you can easily create backups, establish test environments, and transfer data between databases.