INSERT INTO...SELECT for all MySQL columns

mysql select insert-into
Kyle picture Kyle · Mar 9, 2011 · Viewed 262.5k times · Source

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.

Answer

Mark Byers picture Mark Byers · Mar 9, 2011

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.

Related questions

MySQL How do you INSERT INTO a table with a SELECT subquery returning multiple rows?

MySQL How do you INSERT INTO a table with a SELECT subquery returning multiple rows? INSERT INTO Results ( People, names, ) VALUES ( ( SELECT d.id FROM Names f JOIN People d ON d.id = f.id ), ( "Henry" ), ); I WANT to populate …

Read More
MySQL - UPDATE query based on SELECT Query

I need to check (from the same table) if there is an association between two events based on date-time. One set of data will contain the ending date-time of certain events and the other set of data will contain the …

Read More
SQL query return data from multiple tables

I would like to know the following: how to get data from multiple tables in my database? what types of methods are there to do this? what are joins and unions and how are they different from one another? When …

Read More