Insert into table where not exists (and include duplicates).
545955Aug 10 2007 — edited Aug 10 2007Sorry to bother you all again, but I am stuck.
I have a sql insert where not exists clause that is great at comparing rows between two identical tables and inserting into each rows that are missing from one another. I have a requirement to include all duplicate rows as well, but I can't think of a way to do it.
Example:
insert into table1
select * from table1@db.world a
where not exists (select * from table1 b
where NVL(a.col1,'~') = NVL(b.col1,'~')
and ......));
commit;
It does not capture all of a duplicate row (which could be up to 5 records) and inserts it, how do you qualify if a duplicate exists and then insert all iterations into the table?