Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: HELP: Exporting Data From One Table and Importing to Another

Re: HELP: Exporting Data From One Table and Importing to Another

From: Andy Hardy <aph_at_ahardy.demon.co.uk>
Date: Mon, 27 Sep 1999 13:53:47 -0400
Message-ID: <tRbjOTAmH573EwID@ahardy.demon.co.uk>


In article <37EF197A.B2C37FDD_at_prodigy.net>, Michael Greer <mgreer942_at_prodigy.net> writes
>
>
>Andy Hardy wrote:
>
>> In article <37EEA3F6.411B94B4_at_prodigy.net>, Michael Greer
>> <mgreer942_at_prodigy.net> writes
>> >
>> >I need to export all of the data from a table in database A to a table
>> >in database B. While the table in database B has more columns, it does
>> >contain all of the columns necessary to import the database A table
>> >data. However, the column names are not identicle. Therefore, I was
>> >thinking of using the following process to get this done:
>> >
>> >1) Use MS Access and ODBC to export the table data from database A.
>> >2) Rename the columns in Access to match the column names of the target
>> >table in database B.
>> >3) Export the data from Access to the target table in database B.
>> >
>> >Will this work? If not, why? This there a simpler more direct process to
>> >do what I need to do?
>> >
>> >Note: The tables are in different databases but located on the same
>> >server if that makes any difference.
>> >
>> >
>>
>> Why not just insert using select from one table to the other using a
>> database link?
>
>Because I don't know how!!!! This is all new for me. How do I do this?

Assuming that you are currently logged into db_a.

SQL> create database link db_b connect to db_b_user identified by db_b_password using 'db_b_connect_string';

(where db_b_user/password and db_b_connect_string are the usual user/password and connection string you would use to access db_b)

SQL> insert into table_a (val1, val2) (select blah1, blah2 from table_b_at_db_b);

And Bob's your Uncle. The database link is telling the system to access the given table using the logon and connection string specified. If you have the same user and password on db_a and db_b, you could omit the 'connect to... identified by...'.

Andy
P.S You will need 'create database link' privilege to do this - comes as standard with DBA role.
>
>--
>Mike Greer
>
>Explore potential income opportunities with Greer and Associates
>at http://www.ibocity.com/greeraa. Also, Come visit my digital
>photography web site along with a lot of other interesting stuff at
>http://www.greer.simplenet.com.
>

--
Andy Hardy. PGP key available on request


Received on Mon Sep 27 1999 - 12:53:47 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US