How to write MySql single export query

We as a PHP Drupal web platform developer most often has to face the situation to export data from one table to another table of same database or to table of another database (if site uses multiple databases) during the process of any change in normal work flow.

Most of us normally think to export the data from one table to another by firstly querying the required data from source table and then looping through the query result and inserting them to destination table. This can be simply achieved with a single query.

Major drawback of this is that first select query has to be compiled and run in MySQL server then for each query result front-end server has to loop through it and return the corresponding insert query to MySQL, which in turn has to compile and run for each iteration. If lakhs of data is to be exported, then this will be a huge load for front end server.

Let me tell you another option. The single query export approach:

1. If the source table and destination table are both in same database.

           
             $query = db_query(
               "INSERT INTO `destination_table_name` (`title`, `uid`, `created`)
                 SELECT title, uid, created
                 FROM `source_table_name`
                 ORDER BY created DESC
             );
           
         

2. If the source table and destination table are in different database, namely source table is in database_1 and destination table is in database_2.

           
             $query = db_query(
               "INSERT INTO `database_2.destination_table_name` (`title`, `uid`, `created`)
                 SELECT title, uid, created
                 FROM `database_1.source_table_name`
                 ORDER BY created DESC
             );
           
         

Normally this seems weird, but its the real trick. Avoiding of multiple compilation of query in MySQL server and looping in front-end server. Hope this helps!