Some SQL to demonstrate an insert select causing a primary key violation and how to identify the duplicate rows.
drop table t1;
drop table t2;create table t1 (col1 varchar2(32), col2 number, col3 varchar2(32), col4 number );
create table t2 (col1 varchar2(32), col2 number, col3 varchar2(32), col4 number );alter table t1 add primary key (col1,col2,col3,col4);insert into t1 values ('A','1','A',1);
insert into t1 values ('B','1','A',1);
insert into t1 values ('C','1','A',1);
insert into t1 values ('D','1','A',1);
commit;insert into t2 values ('A','1','A',1);— this insert is a clash with t1 table
insert into t2 values ('B','2','A',1);
insert into t2 values ('C','2','A',1);
insert into t2 values ('D','2','A',1);
commit;insert into t1 select * from t2;insert into t1 select * from t2
*
ERROR at line 1:
ORA-00001: unique constraint (PSAMMY.SYS_C005062) violated— use exists and subquery to source to see what rows match in both tables
select col1, col2, col3, col4 from t1
where exists
(select col1, col2, col3, col4 from t2
where t1.col1=t2.col1 and
t1.col2=t2.col2 and
t1.col3=t2.col3 and
t1.col4=t2.col4
);
COL1 COL2 COL3 COL4
——————————
A 1 A 1
— this is indeed the dup row in both tables