Mysql: INSERT into SELET - using 2 database servers

See more:

SQL

MySQL

Hi Everyone,

I would like to humbly ask your advice with this particular sql query.
I am trying to create a MySQL query where i can copy all records from one Database Table (REMOTE SERVER) to Database Table (LOCAL SERVER). I think it would be MySQL Insert Into Select Statement which i found at http://www.w3schools.com/sql/sql_insert_into_select.asp

Example

INSERT INTO tbl_remote_table
col_mname,col_lname,col_username,col_password,col_question,col_answer,col_email,col_status,col_photo)

SELECT col_eid, col_fname, col_mname,col_lname,col_username,col_password,col_question,col_answer,col_email,col_status,col_photo
FROM tbl_local_table

The problem here is the example given above by the source is for 2 tables in one database only.



Example:

REMOTE SERVER:

SERVER IP: '192.168.1.149'
PORT: '3306'
USERNAME: 'root'
PASSWORD: '1234'
Database: 'db_brgy_pamplonados_backup'
Table: 'tbl_remote_table'

LOCAL SERVER

SERVER IP: 'localhost'
PORT: '3306'
USERNAME: 'root'
PASSWORD: '1234'
Database: 'db_brgy_pamplonados'
Table: 'tbl_local_table'


Database Table (REMOTE SERVER) to Database Table (LOCAL SERVER) has the same design below:

Inline image 1




Below are the following fields i want to copy from Table 1 to Table 2

col_eid
col_fname
col_mname
col_lname
col_username
col_password
col_question
col_answer
col_email
col_status
col_photo

Waiting for your positive reply.



Additionally. I will run the said SQL query from an external program (VB.NET)
So that from a scheduled time the program can upload local table copy to the Remote MySQL Server

What I have tried:

Example

INSERT INTO tbl_admin1
(col_eid, col_fname, col_mname,col_lname,col_username,col_password,col_question,col_answer,col_email,col_status,col_photo)

SELECT col_eid, col_fname, col_mname,col_lname,col_username,col_password,col_question,col_answer,col_email,col_status,col_photo
FROM tbl_admin2