MySQL: INSERT INTO … SELECT … FROM … Selectively on Table Columns / Fields to Combine Multiple Tables into One

• inShare

While you can always fulfill tasks such as creating a new table out of existing ones in MySQL by the help of PHP, it’d be much faster and way more efficient with native SQL queries.

Consider when you need to combine a few tables into one, more precisely, you need just certain fields / columns from each of the table and then piece them together into a new one. You can write a PHP script to do that: 1) read the original records from the tables, 2) insert them one by one with a loop. However the insanely better way is to utilize INSERT INTO … SELECT … FROM … a little more sophisticated as:

INSERT INTO new_table (col1, col2, col3) SELECT old_table_1.col2 as col1, old_table_2.col3 as col2, old_table_3.col1 as col3 FROM old_table_1, old_table_2, old_table_3