Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am new to oracle and this might be a simple question for many.

I need to take data from one db and insert it into other db. The databases are on the same version and edition. I am currently using Toad.

All I have been able to find out is to use export and import utilities, which I am not able to execute from my toad client. I'm hoping for a query to retrieve data in a file and then import this file into another database.

share|improve this question
    
Hi, welcome to DBA.SE. Don't mean to be rude, but what have you tried so far, and have you tried to google it? –  Derek Downey May 25 '12 at 15:02
    
yup...but i need to take 32 rows from one table only and insert the same in another db having same table....on internet they talk of exp/imp utilities, which i am not able to execute from my toad client....and to perform this simple task, i hope for a query to retrieve data in a file and then import this file in another db –  Naroji May 25 '12 at 15:38
add comment

3 Answers

You will probably want to create a database link from one database to the other and then do inserts/updates/deletes/merges between the databases using the link. After creating the link it could be as simple as this:

INSERT INTO t2@db2 (SELECT * FROM t1);

After creating the link you can run this statement in Toad, SQL*Plus, SQL Developer or any number of other SQL apps.

share|improve this answer
add comment

If you have Toad I assume you can connect to both databases from it.

  • Select the origin table in the schema browser in Toad, right click, select "Create in another schema" or "Copy Data to another schems".
  • A dialog box appears, select your options as required.
  • then a "Destination" dialog box appears. Select the connection to the destination database and schema of your choice
  • Click on execute.

Toad will show a failure or success message.

Job done!

share|improve this answer
add comment

I don't know, whether Toad is capable of running SQL*Plus commnads. However, you could try running SQL*Plus COPY command for simple copying between databases (alongside with setting ARRAYSIZE and COPYCOMMIT variables, which you can find in SQL*Plus documentation): http://docs.oracle.com/cd/B19306_01/server.102/b14357/apb.htm#CHDEAEDE

If Toad fails running the command, try SQL*Plus itself. Also it is necessary to note, that you can't copy LOB columns via this command.

Database link mentioned above is also an alternative, if you don't need to copy huge number of rows.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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