PHP, MySQL, Drupal, .htaccess, Robots.txt, Phponwebsites

15 Dec 2013

Copy values from one database to another in mysql

                       All of you know, you can copy data within a table and between tables. But can you copy data between two database in mysql. Yes, you can done it in mysql. The following mysql query is used for do this.
Mysql Query:
                         INSERT INTO database2.table_name (column_name)
                              SELECT table_name.column_name FROM table_name


Copy data from one database to another in mysql:


                       Consider following example. You've two databases named with db1 and db2. Each database have a table. Table1 is presented in db1 and Table2 is present in db2.
The Table1 in db1 is look like this:

Mysql at phponwebsites

The Table2 in db2 look like this:

Mysql at phponwebsites

Now we copy data from database db1 to db2. The mysql query is:
                     
                     INSERT INTO db2.Table2(name,image)
                              SELECT Table1.name, Table2.name FROM Table1

Now you will get output as follow as:

copy values from one database to another in Mysql at phponwebsites

                   Now you'll get values in Table2 as like as Table1 in db.

Copy data from one database to another with specific condition in mysql:


                  You can copy only specific values from one database to another in mysql using 'WHERE' condition in mysql. Consider a example: Now we are going to copy named with 'sample1' and 'sample2' from database db1. The mysql query is:

                     INSERT INTO db2.Table2(name,image)
                              SELECT Table1.name, Table2.name FROM Table1
                              WHERE Game_name!='sample3'

Now you'll get output as follow as:

copy values from one database to another with specific condition in Mysql at phponwebsites

Note:
          You can copy the values from one database to another using  this method only if two databases are in same server.

Related Post: