I have to MySQL databases DB1 and DB2, DB1 is on Online server, and DB2 is on local machine(localhost), Now i want to insert some data into DB2's table named db2_table from DB1's table named db1_table using SQL QUERY. So how it is possible?
2 Answers
I think this is not possible with one SQL Query. A SQL-Query can only be executed on one server and he doesn't know the second server. So you have to do this with an application or you have to use the import and export functions of MySql Workbench or PHPMyAdmin.
Here you can find a very similar question:
insert into a MySQL database on a different server
Hope this will help you.
Comments
It may be possible to create 2 simultaneous connections as suggested by @Being Human - and write one query - which would be ideal. However the method below works for me.
1: make a dump file of DB1 from online server
mysqldump -u <db_username> -h <db_host> -p db_name table_name > backup.sql
2: restore dumpfile to local server and database (will restore to same DB)
mysql -u<db_username> -p DB_NAME < backup.sql
3: INSERT into db2_table from db1_table
INSERT INTO DB2.db2_table (<col1>, <col2>, <col3>....)
SELECT <col1>, <col2>, <col3> ...
FROM db1_table;
Making sure the columns returned by the SELECT subquery on db1_table maps into the columns in the db2_table
$con1forDB1to get data &$con2forDB2to insert data that you get fromDB1