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