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
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) */
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;
/