将数据从一个表复制到另一个表。数据库不同，表结构也不同

I am looking for a solutions for my MySQL data copying related problem. I have a table TAB1 in a database DB1 that contains some data. Now I want some of these data rows to be migrated to another table TAB2 to some another database DB2.

我正在为我的MySQL数据复制相关问题寻找解决方案。我在包含一些数据的数据库DB1中有一个表TAB1。现在我想将其中一些数据行迁移到另一个表TAB2到另一个数据库DB2。

What would be an ideal way to write such a SQL script for MySQL server. I cannot write java/php program etc because I don't have access to the code base.

为MySQL服务器编写这样一个SQL脚本的理想方法是什么。我不能写java / php程序等因为我无法访问代码库。

Any example links will be helpful. I know this can be done in Oracle via DBLink but how to do it in MySQL.

任何示例链接都会有所帮助。我知道这可以通过DBLink在Oracle中完成,但如何在MySQL中完成。

Thanks

4 个解决方案

#1

insert into db2.table2 (field1,field2,..,fieldN)
select field1,field2,..,fieldN from db1.table1

EDIT. If you need to do an update between two different databases this is the right syntax:

编辑。如果您需要在两个不同的数据库之间进行更新,这是正确的语法:

update 
db2.table2 as t2,
db1.table1 as t1
set 
t2.field1 = t1.field1,
t2.field2 = t1.field2,
t2.field3 = t1.field3
where t1.id = t2.id

#2

If both databases are on the same server then the easiest way is to use INSERT INTO... SELECT query

如果两个数据库都在同一台服务器上,那么最简单的方法是使用INSERT INTO ... SELECT查询

INSERT INTO
   database2.table2 (c1, c2, c3)
SELECT
   c2, c4, MD5(c3)  --you can choose only these columns that are needed as well as use functions to convert data to required format if needed
FROM
   database1.table1

#3

Find a free ETL tool of some kind:

找到某种免费的ETL工具:

http://searchdatamanagement.techtarget.com/answer/The-ETL-process-and-MySQL

#4

This will copy all the data from one table to another (MySQL)

这会将所有数据从一个表复制到另一个表(MySQL)

INSERT INTO  `databse_name`.`tablename_copy` 
SELECT * 
FROM  `databse_name`.`tablename` ;

if you set primary key this will create some problem. Please check the primary key duplication error

如果你设置主键,这将产生一些问题。请检查主键重复错误

#1

insert into db2.table2 (field1,field2,..,fieldN)
select field1,field2,..,fieldN from db1.table1