如何在2个表之间/在不同数据库中的不同列之间移动数据 [英] How to move data between 2 tables / with different columns in different databases

问题描述

在以下情况下，foo被重命名为foo1.

We have the following scenario where foo has been renamed as foo1.

foo.col1已重命名为foo1.col11.

foo.col2已被删除

实际上，这些表曾经是相似的表，我想将这些表的数据从A复制到B.鉴于表/列名称已更改，我将如何进行简单的迁移.

In fact these used to be similar tables and I would like to copy data from A to B for these tables. How would I go about doing a simple migration given that the table/column names have undergone a change.

Database 'A'
create table foo {id pk, col1 varchar(255), col2 tinyint(1), col3 datetime);
create table foo_bar1 (id pk, foo_id fk, col4 datetime, col5 varchar(255));

Database 'B'
create table foo1 {id pk, col11 varchar(255), col3 datetime);
create table foo1_bar1 (id pk, foo1_id fk, col4 datetime, col5 varchar(255));

推荐答案

您应该可以做到:

INSERT INTO B.foo1 (id, col11, col3) 
 SELECT id,col1,col3 FROM A.foo;

INSERT INTO B.foo1_bar1 (id, foo1_id, col4, col5) 
 SELECT id,foo_id,col4,col5 FROM A.foo_bar1;

这篇关于如何在2个表之间/在不同数据库中的不同列之间移动数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！