How to Efficiently Preserve All Columns When Using MySQL's INSERT INTO...SELECT?

Preserving Column Data with Efficient INSERT INTO...SELECT

When transferring data from one MySQL table to another while retaining all columns, a common challenge arises. Using the format INSERT INTO this_table_archive (*) VALUES (SELECT * FROM this_table WHERE entry_date < '2011-01-01 00:00:00'); may not yield the desired result.

The correct syntax, as outlined in the MySQL manual, is as follows:

INSERT INTO this_table_archive (col1, col2, ..., coln) 
SELECT col1, col2, ..., coln
FROM this_table
WHERE entry_date < '2011-01-01 00:00:00'; 

This revised format explicitly names each column in the target table, ensuring that all data is copied appropriately.

Furthermore, if the id column is configured as auto-increment and both tables contain data, it may be advantageous to exclude it from the column list. This prevents potential conflicts arising from inserting duplicate IDs. However, if the target table is empty, this omission is not necessary.

The above is the detailed content of How to Efficiently Preserve All Columns When Using MySQL's INSERT INTO...SELECT?. For more information, please follow other related articles on the PHP Chinese website!