Q

Can I copy table data with unlike primary keys from Table1 to Table2?

Oracle PL/SQL expert Dan Clamage explains two methods for copying table data from table1 to table2 when the tables have different primary keys in this expert tip.

I have a table

===========

TABLE1

temp1    varchar(10)
temp2    varchar(10)
temp3    varchar(10)
temp4    varchar(10)
temp5    varchar(10)
temp6    varchar(10)

In this table, temp1, temp2, temp3, temp4 are primary key

There is another table

===========

TABLE2

temp1    varchar(10)
temp2    varchar(10)
temp3    varchar(10)
temp4    varchar(10)
temp5    varchar(10)
temp6    varchar(10)

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

from table1;

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.

 

 

This was first published in January 2010

Dig deeper on Using Oracle PL-SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close