Copying Data between Databases

• a remote database to your local database

• your local database to a remote database

• one table on a remote database to another table on that database

• a table on one remote database to a table on another database

• a valid service name for the remote database

• a valid username and password for the remote database

COPY FROM username/password@service_name 
[TO username/password@service_name] 
(APPEND | CREATE | INSERT | REPLACE) 
tablename [columnlist] 
USING subquery 

In this syntax:

service_name	Specifies a valid service name in the TNSNAMES.ORA file. Each service name maps to a connect descriptor that connects to a database using the keyword SID.
APPEND	Specifies that if the destination table already exists, COPY will insert the copied data into it. If the table does not already exist, COPY will create it and then insert the copied data into it.
CREATE	Specifies that COPY will create a new table and insert the copied data into it. If the table already exists, COPY will report an error and stop processing.
INSERT	Specifies that if the destination table already exists, COPY will insert the data into it. If the table does not already exist, COPY will report an error and stop processing.
REPLACE	Specifies that if the table already exists, it will be dropped and replaced with the new data. If the table does not already exist, it will be created and the data inserted into it.
tablename	Specifies the destination table to which you are copying data.
columnlist	Specifies that only data from the specified columns should be copied.
subquery	Specifies the query to be used to select the data to be copied.

Note that you can use any kind of query to select the data to be copied. For example, you can COPY select rows from the source table by using a WHERE clause. You can also COPY specific columns from the source table by listing those columns after the SELECT statement, instead of using the asterisk that means all columns. The copied columns will have the same names in the destination table that they had in the source table.

Following is an example of a COPY command that copies only two columns from the source table specified by the alias BOSTON, and copies only those rows in which the value of DEPTNO is 30. Since the TO clause is omitted, the table is copied to the database to which SQL*Plus is currently connected:

COPY FROM SCOTT/TIGER@BOSTON 
		REPLACE EMPCOPY2 
		USING SELECT ENAME, SAL 
			FROM EMP 
			WHERE DEPTNO = 30; 

In the following example, the command copies the entire table EMP from the remote database specified by the SALES service name in the TNSNAMES.ORA file. Because the TO clause is omitted, the table is copied to the database to which SQL*Plus is currently connected. The new table is named EMP2:

COPY FROM SCOTT/TIGER@SALES 
		CREATE EMP2 
		USING SELECT * FROM EMP; 

You can also copy data to a local table from a remote table using the syntax:

COPY FROM SCOTT/TIGER@servicename 
		INSERT INTO local_table 
		SELECT * FROM remote_table@dblink 

For further information about the COPY command, see the SQL*Plus User's Guide and Reference Manual.