CODEWITHSUNDEEP
oracle-databasedatabase-link
Sam Mathew
Sam Mathew

Reputation: 7

Copy rows from one table to another, ignoring duplicates on remote

I have two table with the same columns in different databases. Both table have records.i want to insert the records of table2 in table1 but i want to ignore those records which are already in table 1. As well i want to store all ignored records in a new table. Example:

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;

Error report - SQL Error: ORA-00001: unique constraint (SCOTT.SYS_C0010807) violated 00001. 00000 - "unique constraint (%s.%s) violated" *Cause: An UPDATE or INSERT statement attempted to insert a duplicate key. For Trusted Oracle configured in DBMS MAC mode, you may see this message if a duplicate entry exists at a different level. *Action: Either remove the unique restriction or do not insert the key.

Upvotes: 0

Views: 351

Answers (1)

Frank Ockenfuss
Frank Ockenfuss

Reputation: 2043

you can use intersect and minus to determine the differences

-- 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;

Upvotes: 0

Related Questions