SQL*PLUS - COPY Statement

Copy from a query into a table.
The COPY command works for remote and even non-oracle databases.

Syntax:

COPY {
     FROM username[/password]@db_spec |
       TO username[/password]@db_spec |
     FROM username[/password]@db_spec TO username[/password]@db_spec
     }
       {APPEND | CREATE | INSERT | REPLACE}
          destination_table 
            [(column, column, column…)]
               USING query
 
Key
 FROM       Source database (defaults to current)

 TO         Destination database (defaults to current)

 USING      The SELECT statement(source of data to copy)

 APPEND     Insert into the destination_table
            COPY will create the destination_table if does not exist.

 CREATE     Create the destination_table and insert the rows.
            CREATE will fail with an error if the destination_table already exists.

 INSERT     Insert into the destination_table
            INSERT will fail with an error if the destination_table does not already exist.
            Columns in the USING query must match those in the destination_table. 

 REPLACE    Replace destination_table and its contents with rows from query.
            REPLACE will drop the destination_table(if it exists)and 
            replace it with a table containing the copied data. 

Columns created with NUMBER datatype will default to NUMBER(38)
A more efficient alternative to COPY… INSERT is CREATE TABLE AS…

Related Oracle Commands:

SET LONG - default width for LONG columns
SET ARRAYSIZE - Fetchsize
SET COPYCOMMIT - no. of fetches between each autocommit