How to copy data from one table to another new table in MySQL?

Copying data from one table to another new table in MySQL can be achieved through several methods. Below are some common approaches:

1. Using the CREATE TABLE ... SELECT Statement

This method directly creates a new table and copies the data. For instance, consider a table named original_table, and we want to copy its data to a new table new_table.

sql
CREATE TABLE new_table AS SELECT * FROM original_table;

This command creates a new_table with the same structure and data as original_table. If only specific columns need to be copied, you can specify the column names in the SELECT statement:

sql
CREATE TABLE new_table AS SELECT column1, column2 FROM original_table;

2. Using the INSERT INTO ... SELECT Statement

This method is appropriate when the new table new_table already exists, and you want to copy data from original_table into it. First, create a new table with a manually defined structure:

sql
CREATE TABLE new_table (
    id INT,
    data VARCHAR(100)
);

Then, use the INSERT INTO ... SELECT statement to copy the data:

sql
INSERT INTO new_table (id, data) SELECT id, data FROM original_table;

This will select the id and data columns from original_table and insert them into new_table.

3. Using the Method of Copying Table Structure and Inserting Data

If you need to copy the table structure instead of the data, you can use the following command to copy the table structure:

sql
CREATE TABLE new_table LIKE original_table;

Then, if necessary, use the INSERT INTO ... SELECT statement to copy the data:

sql
INSERT INTO new_table SELECT * FROM original_table;

Practical Example

In my previous work, we needed to extract historical data from a production table into an archive table to optimize query performance. We first used CREATE TABLE ... LIKE to create a new table with the same structure, then used the INSERT INTO ... SELECT statement to copy data from the original table based on specific date ranges. This not only helped reduce the size of the original table but also made queries on the original table more efficient.

Important Considerations

• When performing large-scale data copying, consider the impact on database performance, and it may be necessary to do this during off-peak hours.
• Ensure that the indexes, foreign keys, and other constraints of the new table match business requirements, as these elements may not be automatically copied from the original table.
• If the new table and the original table reside in the same database, ensure there is sufficient disk space.

These methods offer flexible options for copying data and migrating table structures based on specific requirements.