5

Oracle SQL developer gives us the option of exporting data. But it only gives us the option to export as a set of Insert statements.

I have two databases with a mirrored table. One of them has some column data missing from it. I tried truncating the data from that table but it has foreign keys associated and SQL doesn't let me drop it.

Is there a way to export the data from the larger/fuller table in the form of "Update Table..." commands? I tried googling first, failed. I bet there's a solution for this out there.

If nothing else, I am thinking of making a small python script that modifies the Insert command set into an Update command set after export.

2
  • assume I have to export the data... Things such as "Update table .. where (select something from other table)" is not allowed. This is because I have to create the script and send it to someone...
    – Nitin
    Mar 14, 2012 at 17:25
  • Same question here I posted myself: dba.stackexchange.com/questions/189505/…
    – abbr
    Oct 31, 2017 at 14:15

3 Answers 3

2

Not using a built in command, but...

If an UPDATE really can satisfy your requirements as your title implies, then no inserts should be necessary and the primary key must be in sync between the databases.

Your requirements don't allow remote updates, but if you can remotely query the database, then you can join the local table to the remote table to create the statements that will need to be run the remote system. Here is a demonstration:

drop table t1;
drop table t2;
create table t1 as (select level c1, level c2 from dual connect by level <=7);
create table t2 as (select * from t1);
update t2 set c2 = null where c1 in (3,6);
select * from t1;
select * from t2;

select 'UPDATE t2 SET c2 = ' || t1.c2 || ' WHERE c1=' || t1.c1 || ';' sql from t1 
join t2 on t1.c1 = t2.c1
where t1.c2 <> t2.c2 OR t2.c2 is null;

If you cannot query the remote database, you can update every row like this:

drop table t1;
create table t1 as (select level c1, level c2 from dual connect by level <=7);

select 'UPDATE t2 SET c2 = ' || t1.c2 || ' WHERE c1=' || t1.c1 || ';' sql from t1;
1
  • Leigh... That seems to be the most complete solution I've seen. Thanks! I've also submitting my own version of the solution. I implemented a small script that converts a series of insert commands into update commands...
    – Nitin
    Mar 15, 2012 at 17:10
2

What you want is MERGE.

See Oracle docs for MERGE . Allows you determine if a row should be updated or inserted.

SQL Developer doesn't support generating MERGE statements today. You can go vote for that feature to make a future version though!

0

Thank you all for your contribution.. This is the way I resolved it and I hope that if someone else needs this, they can find it here -

The following is a python script that converts the insert commands into Update commands...

import sys
import re


file1 = open('import.sql')
file2 = open('update.sql','w')
for line in file1:
    #print line
    m = re.search("values \((\d*)",line)
    #print m.group(1)
    code_id = m.group(1)
    #there are 8 columns in the table and we need the last 2
    all_data = re.search("values \((.*)\,(.*)\,(.*)\,(.*)\,(.*)\,(.*)\,(.*)\,(.*)\)\;",line)
    #print all_data.group(7)
    code_gui = all_data.group(7)
    #print all_data.group(8)
    code_ind = all_data.group(8)
    print "UPDATE CODES SET CODE_GUI_MSG="+code_gui+", CODE_INDICATOR="+code_ind+" WHERE CODE_ID="+code_id+";"

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.