Table Insertion - Multiple MySQL Databases

Question

I am using the below Query to try to insert a few rows from table2 in Database2 into table1 from Database1. Both these tables have different schemas.

Both the tables have same structure. I did check other posts with a similar query to no avail. Used the below basic approach :

 INSERT INTO    remoteDATABASE.remoteSCHEMA.remoteTABLE
  SELECT  * FROM    localTABLE

INSERT INTO 'eno*****.com'.asdf_stage.temp 
SELECT i.artf_id as  id,
    title as description,
    (case 
        when i.assigned_group = 'asdfk' then 'dfg'
        when i.assigned_group = 'dfgdg' then 'dgdk'
        when i.assigned_group = 'dfghdgf' then 'igo'
        when i.assigned_group = 'dfgh' then 'eMgem'
        when i.assigned_group = 'Edsfg' then 'esgd'
        end ) as area,
    "N/A" as disposition,
   "********" as flavor,
   "ipf" as link,
   "*************" as vendor,
    DATE(date_created) date,
  DATE(close_date) as completed
  FROM ******com.lkjhg_stage.issues  i
    where date(date_created) >'2018-04-01' AND status!='Rejected'

I am getting syntax error, need to know what is the correct syntax to mention the DB name , Schema name and the table name at both the places. Also, the table has around 10-15 columns , do I need to specify all of them ?

Maybe a duplicate of this one https://stackoverflow.com/questions/22912167/mysql-insert-into-from-one-database-in-another — Sal, Apr 17 '18 at 18:15
I did check that query , it does mention how to specify the schema, also I wanted to know if we need to specify each column by name in the first line.. — Mandara31, Apr 17 '18 at 18:18
I'm not sure I've ever seen two-dot table identifier qualification (in MySQL) before; if you are trying to do an insert...select from one _server_ to another, I am not sure that is possible. — Uueerdo, Apr 17 '18 at 18:22
I am not sure if this is the right syntax, hence the question... Need to know how to specify database name , schema name and table name in the above query. — Mandara31, Apr 17 '18 at 18:23
_Database_ and _schema_ mean the same thing in MySQL. [see here](https://stackoverflow.com/questions/11618277/difference-between-schema-database-in-mysql) Also, "database" != "server" — Uueerdo, Apr 17 '18 at 18:24
Possible duplicate of [MySQL: SELECT from another server](https://stackoverflow.com/questions/508100/mysql-select-from-another-server) — Uueerdo, Apr 17 '18 at 18:28
Al right , so in my query I have specified server name . database name . table name , and I get syntax error ( 1604) , what needs to change there ? — Mandara31, Apr 17 '18 at 18:29

score 0 · Answer 1 · answered Apr 17 '18 at 18:27

Replace the double quotes with single quotes. DATE is not a function, and the reserved word date is not a good column name.

Reference your table as SERVER.DATABASE.OWNER.OBJECT

INSERT INTO SERVER1.DATABASE1.OWNER1.OBJECT1
SELECT * FROM localTABLE

INSERT INTO SERVER1.DATABASE1.OWNER1.OBJECT1
SELECT i.artf_id as id,
    title as description,
    (case 
        when i.assigned_group = 'asdfk' then 'dfg'
        when i.assigned_group = 'dfgdg' then 'dgdk'
        when i.assigned_group = 'dfghdgf' then 'igo'
        when i.assigned_group = 'dfgh' then 'eMgem'
        when i.assigned_group = 'Edsfg' then 'esgd'
        end ) as area,
    'N/A' as disposition,
   '********' as flavor,
   'ipf' as link,
   '*************' as vendor,
    date_created,
    close_date as completed
FROM SERVER2.DATABASE2.OWNER2.OBJECT2  i
WHERE date_created > '2018-04-01' AND status != Rejected'

MySQL doesn't support `SERVER.DATABASE.OWNER.OBJECT` as reference but only `DATABASE.TABLE` as reference — Raymond Nijland, Apr 17 '18 at 18:29

Table Insertion - Multiple MySQL Databases

1 Answers1