asked on

Dump/copy tableA records onto tableB

I've looked around this section for an answer and so far I see answers that require quite a bit of manual work to get going. I am using Oracle 9 with SQL Plus as my client. My version of SQL plus does not have the EXP or IMP command so I am left to think that 'SELECT INTO' or 'COPY' are my only options. Basically, I have tableA and tableB both inside the same schema/database. TableA and tableB are very similar although both have only a few fields that are not on the other table. I have verified that those fields present in tableB and NOT in tableA can be NULL. Now all I want to do is dump/copy all of the fields(available from tableA) from tableA onto tableB (sorry for the extreme repetition). Furthermore, there is one field in tableA that corresponds to tableB although they are named differently (tableA.date_entered_Db -> tableB.date_entered).

Example

tableA
-------------------
Title
Lexis_Cite
Case_Date
Detailed_Form
Date_Entered_Db
Cert_Denied

tableB
-----------------
Title
Lexis_Cite
Case_Date
Date_Entered
Cert_Denied
Precedential

So I want to move the following information:
tableA.Title -> tableB.Title
tableA.Lexis_Cite -> tableB.Lexis_Cite
tableA.Case_Date -> tableB.Case_Date
tableA.Detailed_Form -> Discarded completely
tableA.Date_Entered_Db -> tableB.Date_Entered
tableA.Cert_Denied -> tableB.Cert_Denied
tableB.Precedential -> NULL as per default

Please send explicity instructions on how to do this. Lastly, how would I save my entire schema/database in case I want to restore it to the current status?

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bisonfur37

ASKER

That did the trick. Thank you very much. I hope you don't mind me asking one more thing (I will accept your answer right after I post this). I am using SQL Plus and I really don't like it particularly since it is lacking much needed features. As a matter of fact, I have SQLPlus Worksheet and
Squirrel SQL installed
but for some reason I am having problems with those two although I had them working last week. Squirrel SQL is giving me a Savepoint class not found error and no matter, I do not want to bother the people here. Do you have/recommend any other SQL Plus clients that have some sort of memory and nice features for everyday use?

slightwv (䄆 Netminder)

No problem with the followup questions.

I'm curious. What features are you looking for?

I've never used it but Toad from Quest Software has quite a following and those that use it seem to love it. They have a free trial version.

http://www.toadsoft.com/

bisonfur37

ASKER

You know, I really like Squirrel SQL because it was easy to set up DB drivers and then attach an alias to one of those and then you are set. What I need is to be able to view the entire database and have a command history. For some reason it is not working now, probably due to a Java update that I ran last week so now the Savepoint class is all screwed up. Toad would be cool but it is expensive. Do you know of any other clients?

slightwv (䄆 Netminder)

Other than Enterprise Manager to get a view of the database. Nope. Sorry.

Actually, I had never heard of Squirrel. Thanks for that!!!!

bisonfur37

ASKER

All I had to do to fix Squirrel SQL was to fix the JAVA_HOME variable that was probably messed up by the Java update thingy. No matter. I am done.