How to Correctly Insert All Columns from One Table to Another in MySQL Using INSERT INTO...SELECT?

Insert All Columns into Target Table Using INSERT INTO...SELECT

You're facing difficulties copying data from this_table to this_table_archive using an INSERT INTO statement with wildcard (*) selection. Let's troubleshoot the issue and find a working solution.

The error lies in using (*) to select all columns without specifying the column names. The correct syntax, as detailed in the MySQL manual, requires you to list the column names explicitly.

Here's the correct code:

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

Replace col1, col2, ..., coln with the actual column names in your table.

Regarding the id column, if both tables have an auto-increment id and data already exists in either table, you may consider omitting id from the column list in your query to avoid potential duplicate key conflicts.

For an empty target table, this omission won't cause any issues. However, for tables with data, excluding the id column will ensure new, unique IDs are generated for the copied rows.

The above is the detailed content of How to Correctly Insert All Columns from One Table to Another in MySQL Using INSERT INTO...SELECT?. For more information, please follow other related articles on the PHP Chinese website!