How to skip same duplicate key data when copy table

oracle sqlbulkcopy sql-insert subquery

Question

I have a table1(key is X, XX) looks like below:

x,y,xx,yy,xxx,yyy

I created a new table2 (key is code) as below:

code,name,xx,yy,xxx,yyy

Now I wan to copy all data in table1 to table2, and if met same code skip it,

x -> code
Y -> name
xx-> xx
yy -> yy
xxx -> xxx
yyy -> yyy

I used code as below to copy all data, I got error 00001. 00000 - "unique constraint (%s.%s) violated" because there is duplicated x in table1, but I am not sure how to skip duplicated X data, can you help me?

INSERT INTO table2 (code, name, xx, yy, xxx, yyy) 
SELECT x, y, xx, yy, xxx, yyy FROM table1

I tried this, I think it is not correct.

INSERT INTO table2 (code, name, xx, yy, xxx, yyy) 
SELECT DISTINCT x, y, xx, yy, xxx, yyy FROM table1

Accepted Answer

try this:

INSERT INTO table2 (code, name, xx, yy, xxx, yyy) 
SELECT DISTINCT x, y, xx, yy, xxx, yyy FROM table1
where x not in (select code from table2)

OR

use hint /*+ ignore_row_on_dupkey_index(table2, table2_index) */


Popular Answer

You can try with cursor :

DECLARE
Cursor c1 is select code, name, xx, yy, xxx, yyy from table1;

for insertValue in c1
loop

insert into table2(code, name, xx, yy, xxx, yyy) values (insertValue.code, insertValue.name,insertValue.xx ... );

end loop;

Few insert will fail because of duplication , but rest should be inserted.

Or other option without cursor is :

BEGIN
 FOR insertValue IN (
 select code, name, xx, yy, xxx, yyy from table1 )
LOOP
insert into table2(code, name, xx, yy, xxx, yyy) values (insertValue.code, 
insertValue.name,insertValue.xx ... );  
END LOOP;
END;
/



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why