How to use Oracle’s SQL*Plus COPY command

In an effort to move tables from our Development to our Test and Production environments, I came across the very useful and easy to use COPY command. The COPY command in SQL*Plus, allows you to copy tables from one server to another (or even within the same server) in one quick step. Here is the Syntax of the command as provided by Oracle.

COPY {FROM database | TO database | FROM database TO database} -
{APPEND|CREATE|INSERT|REPLACE} -
destination_table [(column, column, column, ...)] -
USING query

FROM database, is the database you are copying from, and TO database, is the destination database.

The database format is like so: username[/password]@connect_identifier The connect identifier should be you Oracle Net connection string.

You can perform the following actions to the destination database table.

APPEND, will insert the returned rows from the query to the existing table. The table is created if it does not exist. CREATE, will first try and create a table, and then add the returned rows from the query to it. If the table exists, an error is returned. INSERT, will insert the returned rows from the query to the existing table. If the table does not exist, an error is returned. An Insert must use a SELECT query that returns a column for each column in the destination table. REPLACE, will add the returned rows from the query to the destination table, after it drops the existing table, and replaces it with a new one. If the table does not exist, it will be created.

(column, column, column, …), specifies the columns destination table. If you do specify column names, you must make sure that they are returned exactly in the same order in your query. The query must also return the same number of columns as you have in your list.

If you are connected to either one, you can omit the FROM or TO respectively. I find that the easiest way is to first log in to either the TO server, and issue the COPY command providing the database credentials of the FROM server respectively.

Examples:

Assume source database SRCDB and destination database DESTDB. Let’s also assume that you want to copy data from source table SRCTable on SRCDB to DESTTable in DESTDB.

To copy data from the source to destination table, the command syntax will be something like:
COPY FROM username/password@SRCDB TO username/password@DESTDB APPEND –
DESTTable (c1, c2, c3) USING SELECT c1,c2,c3 FROM SRCTable

To create an exact copy of the source table in the destination server, if it does not exist:
COPY FROM username/password@SRCDB TO username/password@DESTDB CREATE –
DESTTable USING SELECT * FROM SRCTable

If you already connect to the destination server for example, you can omit the TO database syntax and use something like this:
COPY FROM username/password@SRCDB CREATE -
DESTTable USING SELECT * FROM SRCTable

If you were only copying a selection of the rows of the source table to the destination table, you can customize your query by applying the appropriate filters. (Assuming that column c1 is numeric):
COPY FROM username/password@SRCDB TO username/password@DESTDB -
CREATE DESTTable USING SELECT * FROM SRCTable WHERE c1 > 100