Copies the data from a query to a table in a local or remote database. COPY supports the following datatypes:
COPY {FROM database | TO database | FROM database TO database}
{APPEND|CREATE|INSERT|REPLACE} destination_table
[(column, column, column, ...)] USING query
where database has the following syntax:
username[/password]@connect_identifier
Refer to the following list for a description of each term or clause:
Specifies the database that contains the data to be copied. If you omit the FROM clause, the source defaults to the database to which SQL*Plus is connected (that is, the database that other commands address). You must include a FROM clause to specify a source database other than the default.
Specifies the database containing the destination table. If you omit the TO clause, the destination defaults to the database to which SQL*Plus is connected (that is, the database that other commands address). You must include a TO clause to specify a destination database other than the default.
Specifies username[/password] @connect_identifier of
the Oracle source or destination database you wish to COPY FROM or COPY
TO. If you do not specify password in either the FROM clause or the
TO clause, SQL*Plus will prompt you for it. SQL*Plus suppresses the
display of your password response.
You must include the connect_identifier clause which consists of a Net8 connection string, to specify the source or destination database. The exact syntax depends upon the Net8 communications protocol your Oracle installation uses. For more information, refer to the Net8 manual appropriate for your protocol or contact your DBA.
Inserts the rows from query into destination_table if the table exists. If destination_table does not exist, COPY creates it.
Inserts the rows from query into destination_table after first creating the table. If destination_table already exists, COPY returns an error.
Inserts the rows from query into destination_table. If destination_table does not exist, COPY returns an error. When using INSERT, the USING query must select one column for each column in the destination_table.
Replaces destination_table and its contents with the rows from query. If destination_table does not exist, COPY creates it. Otherwise, COPY drops the existing table and replaces it with a table containing the copied data.
Represents the table you wish to create or to which you wish to add data.
Specifies the names of the columns in
destination_table. You must enclose a name in double quotes if it
contains lowercase letters or blanks.
If you specify columns, the number of columns must equal the number of columns selected by the query. If you do not specify any columns, the copied columns will have the same names in the destination table as they had in the source if COPY creates destination_table.
Specifies a SQL query (SELECT command) determining which rows and columns COPY copies.
To enable the copying of data between Oracle and non-Oracle
databases, NUMBER columns are changed to DECIMAL columns in the destination
table. Hence, if you are copying between Oracle databases, a NUMBER column with
no precision will be changed to a DECIMAL(38) column. When copying between
Oracle databases, you should use SQL commands (CREATE TABLE AS and INSERT) or
you should ensure that your columns have a precision specified.
The SQL*Plus SET variable LONG limits the length of LONG columns
that you copy. If any LONG columns contain data longer than the value of LONG,
COPY truncates the data.
SQL*Plus performs a commit at the end of each successful COPY. If
you set the SQL*Plus SET variable COPYCOMMIT to a positive value n,
SQL*Plus performs a commit after copying every n batches of records. The
SQL*Plus SET variable ARRAYSIZE determines the size of a batch.
Some operating environments require that service names be placed in
double quotes.
The following command copies the entire EMP table to a table named WESTEMP. Note that the tables are located in two different databases. If WESTEMP already exists, SQL*Plus replaces the table and its contents. The columns in WESTEMP have the same names as the columns in the source table, EMP.
SQL> COPY FROM SCOTT/TIGER@HQ TO JOHN/CHROME@WEST - > REPLACE WESTEMP - > USING SELECT * FROM EMP
The following command copies selected records from EMP to the database to which SQL*Plus is connected. SQL*Plus creates SALESMEN through the copy. SQL*Plus copies only the columns EMPNO and ENAME, and at the destination names them EMPNO and SALESMAN.
SQL> COPY FROM SCOTT/TIGER@HQ - > CREATE SALESMEN (EMPNO,SALESMAN) - > USING SELECT EMPNO, ENAME FROM EMP - > WHERE JOB='SALESMAN'