[SOLVED] INSERT INTO...SELECT for all MySQL columns

INSERT INTO...SELECT for all MySQL columns

I'm trying to move old data from:

this_table >> this_table_archive

copying all columns over. I've tried this, but it doesn't work:

INSERT INTO this_table_archive (*) VALUES (SELECT * FROM this_table WHERE entry_date < '2011-01-01 00:00:00');

Note: the tables are identical and have id set as a primary key.

Solution

The correct syntax is described in the manual. Try this:

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

If the id columns is an auto-increment column and you already have some data in both tables then in some cases you may want to omit the id from the column list and generate new ids instead to avoid insert an id that already exists in the original table. If your target table is empty then this won't be an issue.