将行从一个表复制到另一个表，忽略远程上的重复项

Question

我有两个表，在不同的数据库中具有相同的列。两个表都有记录。我想在表 1 中插入表 2 的记录，但我想忽略表 1 中已有的那些记录。我还想将所有忽略的记录存储在一个新表中。例：

create table dest 
(id number primary key,
 col1 varchar2(10));
create table src
(id number,
 col1 varchar2(10));
insert into src values(1,'ABC');
insert into src values(2,'GHB');
insert into src values(3,'DUP');
insert into src values(3,'DUP');
commit;
merge into dest 
  using 
  (select id,col1 from src) src on(dest.id=src.id)
when not matched then 
  insert values(src.id,src.col1)
when matched 
  then update set dest.col1=src.col1;

错误报告 -SQL 错误： ORA-00001： 违反唯一约束 （SCOTT.SYS_C0010807）00001. 00000 - "违反唯一约束 （%s.%s）"*原因：更新或插入语句试图插入重复的键。 对于在 DBMS MAC 模式下配置的受信任的预言机，您可能会看到 如果其他级别存在重复条目，则此消息。*操作：删除唯一限制或不插入密钥。

Answer 1

您可以使用intersect和minus来确定差异

-- Test Data
-- table1@abc  
with data1(id,
val) as 
 (select 1, 'val1'
    from dual
  union all
  select 2, 'val2'
    from dual
  union all
  select 3, 'val3'
    from dual),
-- table2@xyz  
data2(id,
val) as 
 (select 1, 'another val1'
    from dual
  union all
  select 2, 'val2'
    from dual
  union all
  select 4, 'val4'
    from dual)
-- Intersection
select 'Intersection', intersection.*
  from ((select * from data2) intersect (select * from data1)) intersection
union all
-- data2 not in data1
select 'data2 not in data1', d2.*
  from ((select * from data2) minus (select * from data1)) d2
union all
-- data1 not in data2
select 'data1 not in datad', d1.*
  from ((select * from data1) minus (select * from data2)) d1;