Talk:Merge

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.

-------------------

Wiki is not the place to post a question but new page or comment on page.

Try to post it on Forum: http://www.orafaq.com/forum/i/102589/ without forget to before read OraFAQ Forum Guide (http://www.orafaq.com/forum/t/88153/0/) and "How to use [code] tags" topic (http://www.orafaq.com/forum/t/171557/102589/) and make your code easier to read.

Michel Cadot (talk) 10:08, 4 March 2016 (CET)