mysql - 表插入 - 多个 MySQL 数据库

Question

我正在使用以下查询尝试将 Database2 中的 table2 中的几行插入到 Database1 中的 table1 中。这两个表都有不同的架构。

两个表具有相同的结构。我确实检查了其他具有类似查询的帖子，但无济于事。使用以下基本方法:

 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'

我收到语法错误，需要知道在这两个地方提及数据库名称、架构名称和表名称的正确语法是什么。另外，该表大约有 10-15 列，我需要指定所有列吗？

Answer 1

将双引号替换为单引号。 DATE 不是函数，保留字 date 也不是一个好的列名。

将您的表引用为 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'