CODEWITHSUNDEEP
sqloracle-database
robben
robben

Reputation: 181

How to copy a table from one database to another in Oracle

I am using Oracle as my database.

I have a QA environment (QA_1) and now I just finished creating another new QA environment (QA_2). However, I have to copy some data from QA_1 table to QA_2.

How do I copy from one database table to another? So something similar to the following syntax:

INSERT INTO QA_2.app_prop (prop_id, prop_name, prop_value)
SELECT prop_id, prop_name, prop_value FROM QA_1.app_prop WHERE prop_id IN 
(SELECT prop_id FROM app_env WHERE function = 'CCC');

My user connection to QA_1 is: 

host name: uat-blah.foo.foo.com
port: 2501
servicename: uat

I have looked around google and here on stackoverflow and I couldn't find any syntax that does what I am looking for.

I have tried the syntax for SQL plus

COPY FROM username/password@connectionstring-
TO username/password@connectionstring -

but to no avail.

Upvotes: 2

Views: 8738

Answers (1)

atokpas
atokpas

Reputation: 3351

The COPY command will be obsoleted in future releases of SQL*Plus. SQL*Plus User's Guide and Reference-Release 9.2

There are several methods like using datapump or CTAS. You can also use Database Link.

SQL> CREATE DATABASE LINK dblink_QA_1
CONNECT TO QA_1_app_prop_SCHEMA
IDENTIFIED BY password
USING '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = uat-blah.foo.foo.com)(PORT = 2501)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = uat)))';

And change your insert statement to-

SQL> INSERT INTO QA_2.app_prop (prop_id, prop_name, prop_value)
SELECT prop_id, prop_name, prop_value FROM QA_1.app_prop@dblink_QA_1 WHERE prop_id IN 
(SELECT prop_id FROM app_env@dblink_QA_1 WHERE function = 'CCC');

Upvotes: 3

Related Questions