Inserting records from a SELECT statement (MySQL)

September 9, 2008

I often forget even simple syntax that makes MySQL operation easy. I’m just adding this basically for personal reference.

Using INSERT statement combined with the SELECT statement you could actually add records from one table to another table. Adding a WHERE clause could enable you to filter the records to copy. Below is the syntax.

INSERT INTO table_destination SELECT * FROM table_source [WHERE condition];

However this generic statement only works if the two tables have the same order of columns and has compatible data types. If that is not the case you would need to specify the columns like the following syntax:

INSERT INTO table_destination (column_1, column_2, column_3)
SELECT column_1, column_2, column_3 FROM table_source [WHERE condition];

Filed under: MySQL | Comments (0)
Related Links: