I have a table
In this table, temp1, temp2, temp3, temp4 are primary key
There is another table
In this table, temp1, temp2, temp3, temp5 are primary keys
Now I want to copy all the data from table1 to table2. The problem is that in table1, the field temp5 can have the same value, but in table 2 temp5 is the primary key.
How can I copy the table data?
I have to wonder about a table where all columns are named temp and have all the same type and length; two such tables don’t seem to readily map to each other.
The non-unique temp5 in table1 is only a problem if the combination of columns temp1-temp3 isn’t unique.
If the columns really are named like this, then why not simply map table1’s primary key columns to table2’s? I.e.,
Insert into table2 (
temp1, temp2, temp3, temp5, -- primary key
temp4, temp6 -– non-key columns
Select temp1, temp2, temp3, temp4, –- primary key
temp5, temp6 -– non-key columns
Once table2 is loaded, then you can figure out what you want to do with the mismatched columns temp4 vs. temp5.
But if you really must match the columns precisely, then you can throw the duplicate rows to some other table and decide on their fate separately. Perhaps there aren’t very many of them.
You can do this in one of two ways:
- Fetch rows one at a time from table1 and insert them into table2. Catch the dup_val_on_index exception and throw the row into an “exceptions” table of your design. It’ll probably look a lot like table1.
- Temporarily disable the primary key constraint, load the rows, then re-apply the primary key constraint and throw the offending rows’ urowids into an exceptions table. But note the primary key won’t exist until you fix all the duplicates. The exceptions table must be created with the Oracle-provided script UTLEXPT1.SQL.
Dig Deeper on Using Oracle PL-SQL
Related Q&A from Dan Clamage
A reader asks Oracle expert Dan Clamage about a single date condition mucking up a dynamic query. Continue Reading
Expert Dan Clamage explains how to use SQL SELECT and SQL UNION ALL statements to sort and visualize a set of sales figures. Continue Reading
One reader asks expert Daniel Clamage about the PL/SQL to_date and to_char functions and how to properly convert date and string values. Continue Reading