将数据从一个表复制到另一个 - Oracle
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14817261/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Copy data from one table to another- Oracle
提问by Femme Fatale
I have two tables namely PERSON and WIFE. I want to make WIFE's data available in PERSON table while keeping entries of WIFE maintained and at the same time adding some the values of PERSON against the data of wife.
我有两张桌子，分别是 PERSON 和 WIFE。我想让 WIFE 的数据在 PERSON 表中可用，同时保持 WIFE 的条目，同时根据妻子的数据添加 PERSON 的一些值。
PERSON Table
人表
PK NAME ADDRESS IS_MARRIED
1 John ab city Y
2 Varvatos cd town N
3 Smith ef town Y
4 Henry gh city Y
5 Lynda gh city Y
WIFE table
老婆表
PK PERSON_ID (FK) NAME
1 1 Alice
2 3 Rosy
3 4 Lynda
Now i want to copy data of WIFE table into PERSON table like this
PERSON table
现在我想像这个PERSON 表一样将 WIFE 表的数据复制到 PERSON 表
中
PK NAME ADDRESS IS_MARRIED
1 John ab city Y
2 Varvatos cd town N
3 Smith ef town Y
4 Henry gh city Y
5 Lynda gh city Y
6 Alice ab city Y
7 Rosy ef town Y
As in the given example you might have noticed that ADDRESS of wife is same as of her spouse and same goes for IS_MARRIED column. Moreover, the PK is also not duplicated. How to go about this?
*Edited*
Another important factor is Lynda already exits in PERSON table, therefore, i certainly don't want to duplicate her entry.
在给定的示例中，您可能已经注意到妻子的地址与她的配偶的地址相同，并且 IS_MARRIED 列也是如此。而且，PK也不重复。如何解决这个问题？
*编辑*
另一个重要因素是 Lynda 已经在 PERSON 表中退出，因此，我当然不想复制她的条目。
回答by Mohsen Heydari
declare
newId number;
begin
select nvl(max(person.pk),0) + 1 into newId from person;
for x in (
select w.Name, p.Address
from wife w inner join Person p
on w.Person_id = P.pk) loop
insert into Person(pk, Name,Address,Is_Married) values (newId ,x.Name ,x.Address,'Y');
newId := newId +1;
end loop;
commit;
end
回答by Art
Use CTAS-create table table_name as select whatever you need from both tables. Simply write a join and add create table as... above the select keyword. If you prefer insert as in Gordon's example and your table is big then you may add an Append hint to your insert...
使用 CTAS-create table table_name 从两个表中选择您需要的任何内容。只需编写一个连接并在 select 关键字上方添加 create table as... 。如果您更喜欢 Gordon 示例中的 insert 并且您的表很大，那么您可以在插入中添加一个 Append 提示...
回答by Gordon Linoff
Try this:
尝试这个：
insert into Person(Name, Address, Is_Married)
select w.name, p.address, 'Y'
from wife w left outer join
Person p
on w.Person_id = person.pk
回答by user2001117
Hi Please try the below code: This meet your requirment
嗨，请尝试以下代码：这符合您的要求
declare PKId number;
begin
select nvl(max(person.pk),0) + 1 into PKId
from person;
for x in (select w.Name, p.Address
from wife w
inner join Person p on w.Person_id = P.pk
) loop
insert into Person(pk, Name,Address,Is_Married)
values (PKId ,x.Name ,x.Address,'Y');
PKId := PKId +1;
end loop;
commit;
end
