Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to copy data from one database/table to another database/table

I have written the following query using the documentation at: Oracle Documentation to copy some data from a database/table on my production server to database/table on Sandbox server.

COPY FROM username1/passwd1@<production_IP> to username2/passwd2@<sandbox_IP> INSERT TABLE_C (*) USING (SELECT * FROM TABLE_C WHERE COL_A = 4884);

However, I am constantly running into Connection failed error. Is there anything wrong with the query?

like image 755
name_masked Avatar asked Jan 20 '12 15:01

name_masked


People also ask

How do I copy data from one table to another table in another database?

Using SQL Server Management StudioOpen the table with columns you want to copy and the one you want to copy into by right-clicking the tables, and then clicking Design. Click the tab for the table with the columns you want to copy and select those columns. From the Edit menu, click Copy.

How do I copy data from one table of one database to another table of another database in SQL?

Enter the data source, server name and select the authentication method and the source database. Click on Next. Now, enter the destination, server name, authentication method and destination database then click on Next. Select 'Copy data from one or more tables or views' option in the next window and click on Next.


3 Answers

The following is the solution that I used. I created a link the remote database then used an INSERT command to populate the data.

CREATE DATABASE LINK database_link_name 
CONNECT TO my_user_name IDENTIFIED BY my_password
USING 'tns_name';

INSERT INTO my_table SELECT * FROM my_remote_table@database_link_name;

If you want to get rid of the database link after the work. Use the following:

DROP DATABASE LINK database_link_name;

See this link for helpful information: https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:9532217300346683472

like image 114
Steve Trautmann Avatar answered Nov 12 '22 22:11

Steve Trautmann


In a typical Oracle environment, you have TNS names set up. That's a service to lookup the connection parameters for Oracle instances given an SID or service name. In it's simplest form, TNS names is a file called tnsnames.ora located by the environment variable TNS_ADMIN (which points to the directory where the file is).

Given the SIDs PROD and SANDBOX, you can then copy the tables from the SQLPLUS command line utility:

COPY FROM username1/passwd1@PROD to username2/passwd2@SANDBOX
    INSERT TABLE_C (*) USING (SELECT * FROM TABLE_C WHERE COL_A = 4884);

Please note that this COPY command only supports a limited set of Oracle datatypes: char, date, long, varchar2, number.

If you don't have TNS names set up, you'll need to know the host name or IP address, the port number and the service name. The syntax then becomes:

COPY FROM username1/passwd1@//192.168.3.17:1521/PROD_SERVICE to username2/passwd2@//192.168.4.17:1521/SANDBOX_SERVICE
    INSERT TABLE_C (*) USING (SELECT * FROM TABLE_C WHERE COL_A = 4884);

To determine the SID and/or service name, you best have a look into the TNSNAMES.ORA file on the database server itself. If you are able to login to the database, you can use the following queries to determine the SID and service name (but don't ask me which is which):

select name from v$database;

select * from global_name;

select instance_number, instance_name, host_name from v$instance;
like image 35
Codo Avatar answered Nov 12 '22 21:11

Codo


Copy gpl_project/gpl_project@gpldatar to gpl_project/gpl_project@gplrdp. Replace BGROUPMASTER using select * from BGROUPMASTER.

like image 27
susheel Avatar answered Nov 12 '22 21:11

susheel