INSERT INTO SELECT command on Mysql Database
Sometimes we need to copied a small pieces of one table to another table. Usually we run SELECT FROM source table first, and then iteratively add into target table, the query is something like this
mysql_query(‘SELECT id, name, address FROM member’);
and then using php, we add all results into target table
mysql_query(‘INSERT INTO target_table(id, name,date_f) VALUES (‘. $id .’, ‘. $name .’, NOW())’);
but using this way we need at least 3 lines code and it is not efficient, there is another way to do that more efficient. Mysql has INSERT INTO SELECT FROM query command, the query will look like below
mysql_query(‘INSERT INTO target_table (id, name, date_f)
SELECT source_table.id, source_table.name, NOW()
FROM source_table;’);
if you want to overwrite old value, you can use REPLACE instead INSERT.
