How to skip same duplicate key data when copy table

oracle sqlbulkcopy sql-insert subquery

Question

I have a table1 with the key "X, XX" that looks like this:

x,y,xx,yy,xxx,yyy

I created a new table2 with the following key: code:

code,name,xx,yy,xxx,yyy

I now want to replicate all of the data from table 1 to table 2, skipping any instances of the same code.

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

To copy all data, I used the code below, but I received an error.00001. 00000 - "unique constraint (%s.%s) violated" X is duplicated in table 1, however I'm not sure how to avoid redundant X data; could you please assist me?

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

I believe that is incorrect, having tested it.

INSERT INTO table2 (code, name, xx, yy, xxx, yyy) 
SELECT DISTINCT x, y, xx, yy, xxx, yyy FROM table1
1
1
6/28/2017 5:34:49 AM

Accepted Answer

Attempt 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

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

1
6/28/2017 10:41:32 AM

Popular Answer

Try this with a 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 inserts will fail due to duplication, but the majority should be.

A different choice without a 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;
/


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow