A Comprehensive Guide to Data Migration Between Tables in MySQL Using INSERT INTO SELECT

Fundamental Principles and Core Syntax of Data Migration

Data migration is a common yet critical operation in database management systems. When transferring data that meets specific criteria from one table to another with identical structure, MySQL offers the INSERT INTO SELECT statement as a standard solution. Its primary advantages lie in simplicity and efficiency, enabling query and insertion operations to be completed with a single SQL command.

The basic syntax is as follows:

INSERT INTO target_table SELECT * FROM source_table WHERE condition;

Using the example from the Q&A, assume two structurally identical tables, customer_table and persons_table, both containing person_id, person_name, and person_email columns. To migrate records where person_name is 'tom' from customer_table to persons_table, execute:

INSERT INTO persons_table SELECT * FROM customer_table WHERE person_name = 'tom';

This statement first executes the SELECT clause to retrieve all rows meeting the condition from customer_table, then directly inserts the result set into persons_table. Since the tables share the same structure, explicit column names are unnecessary; the * wildcard ensures data is transferred in the corresponding column order.

Data Deletion and Atomic Operations

After data insertion, it is often necessary to remove migrated records from the source table to avoid redundancy. This can be achieved with a DELETE statement:

DELETE FROM customer_table WHERE person_name = 'tom';

However, executing INSERT and DELETE statements separately carries risks: if a system failure or concurrent operation interference occurs between them, data inconsistency may result (e.g., data is inserted but not deleted, leading to duplicates). To ensure atomicity, encapsulate these steps within a transaction:

START TRANSACTION;
INSERT INTO persons_table SELECT * FROM customer_table WHERE person_name = 'tom';
DELETE FROM customer_table WHERE person_name = 'tom';
COMMIT;

By wrapping the operations with START TRANSACTION and COMMIT, MySQL treats them as a single logical unit. Changes are permanently applied only if all statements succeed; if an error occurs mid-process, ROLLBACK can revert to the pre-transaction state, maintaining data integrity.

Performance Optimization and Alternative Approaches

While the INSERT INTO SELECT statement is concise and efficient, performance optimization should be considered for large datasets or complex conditions. For instance, Answer 3 in the Q&A proposes a timestamp-based optimization:

START TRANSACTION;
set @N := (now());
INSERT INTO table2 select * from table1 where ts < date_sub(@N,INTERVAL 32 DAY);
DELETE FROM table1 WHERE ts < date_sub(@N,INTERVAL 32 DAY);
COMMIT;

This method pre-calculates the timestamp variable @N and reuses it in both INSERT and DELETE statements, reducing computational overhead. For queries involving time ranges, this optimization significantly improves execution efficiency, particularly in scenarios like periodic archiving of historical data.

Additionally, Answer 2 demonstrates an alternative approach with explicit column names:

INSERT INTO Persons_Table (person_id, person_name, person_email)
SELECT person_id, customer_name, customer_email
FROM customer_table
WHERE "insert your where clause here";

Although this increases code volume, it offers greater flexibility when table structures are not fully identical or column renaming is required. For example, if source and target tables have different column names (e.g., customer_name maps to person_name), explicit mapping ensures correct data transfer. However, for tables with identical structures, using the * wildcard is generally simpler and less error-prone.

Practical Recommendations and Considerations

In real-world applications, the following factors should be carefully evaluated before executing data migration:

First, always back up source table data, especially in production environments. While transaction mechanisms provide some protection, human errors or unforeseen issues can still lead to data loss. A quick backup can be created with CREATE TABLE backup_table AS SELECT * FROM source_table;.

Second, assess the impact of indexes and constraints. If the target table has defined primary keys, unique indexes, or foreign key constraints, inserting duplicate or invalid data may cause errors. Consider temporarily disabling relevant constraints before migration, or use clauses like INSERT IGNORE or ON DUPLICATE KEY UPDATE to handle conflicts.

Finally, monitor execution performance. For large-scale data migration, use EXPLAIN to analyze query plans and optimize index usage in WHERE conditions. If necessary, process data in batches (e.g., via LIMIT clauses) to reduce lock contention and resource consumption.

In summary, INSERT INTO SELECT combined with transaction handling is a reliable method for data migration between tables in MySQL. By understanding its principles and applying optimization strategies, developers can perform data operations efficiently and securely.