SQL*Plus User's Guide and Reference Release 8.1.6 A75664-01 |
|
Command Reference, 17 of 52
Copies the data from a query to a table in a local or remote database.
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]@net_service_name
Refer to the following list for a description of each term or clause:
FROM
database
TO
database
database
Specifies username[/password] @net_service_name 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 net_service_name 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.
APPEND
CREATE
INSERT
REPLACE
destination_table
(
column
,
column
,
column
, ...)
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.
USING
query
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'
|
![]() Copyright © 1999 Oracle Corporation. All Rights Reserved. |
|