?
Solved

Dump/copy tableA records onto tableB

Posted on 2005-03-21
6
Medium Priority
?
1,217 Views
Last Modified: 2011-09-20
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?
0
Comment
Question by:bisonfur37
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 13595325
To clarify some wording:  exp/imp are not sql*plus commands.  But you shouldn't need them for this issue (assuming I understand it).


From what I understand all you wish to do is take data from tableA and insert it into tableB.

If that's true, give this a try( it is untested but should be close):

insert into tableB(title,case_date,date_entered,cert_denied) (select title,case_date,detailed_form,date_entered_db,cert_denied from tableA);
0
 
LVL 2

Author Comment

by:bisonfur37
ID: 13595712
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?
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 13595836
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/
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 2

Author Comment

by:bisonfur37
ID: 13595935
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?
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 13595967
Other than Enterprise Manager to get a view of the database.  Nope.  Sorry.

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

Author Comment

by:bisonfur37
ID: 13617049
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.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question