You control the treatment of the destination table by entering one of four control clauses-REPLACE, CREATE, INSERT, or APPEND. You may find it easier to enter and edit long COPY commands in command files rather than trying to enter them directly at the command prompt.Ĭontrolling Treatment of the Destination Table Here is an example of a COPY command that copies only two columns from the source table, and copies only those rows in which the value of DEPARTMENT_ID is 30: You can use any form of the SQL SELECT command to select the data that the COPY command copies. The USING clause specifies a query that names the source table and specifies the data that COPY copies to the destination table. 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. Hence, if you are copying between Oracle databases, a NUMBER column with no precision will be changed to a DECIMAL(38) column. To enable the copying of data between Oracle and non-Oracle databases, NUMBER columns are changed to DECIMAL columns in the destination table. If you enter any column names, you must enter a name for every column you are copying. If you want to give new names to the columns in the destination table, enter the new names in parentheses after the destination table name. For more information, see the section "Controlling Treatment of the Destination Table" later in this chapter.īy default, the copied columns have the same names in the destination table that they have in the source table. The COPY command behaves differently based on whether the destination table already exists and on the action clause you enter (CREATE in the example above). However, including both clauses benefits the readability of your scripts. When you copy between remote databases, you must include both clauses. When you copy to a remote database from your local database, you can omit the FROM clause. When you copy to your local database from a remote database, you can omit the TO clause. For information on what databases are available to you, contact your DBA. COPY obeys Oracle security, so the username you specify must have been granted access to tables for you to have access to tables. To specify a database in the FROM or TO clause, you must have a valid username and password for the local and remote database(s) and know the appropriate Oracle Net service name(s). You enter the COPY command in the following form:ĭestination_table (column_name, column_name, -ĬREATE NEWDEPT (DEPARTMENT_ID, DEPARTMENT_NAME, CITY). You should use SQL commands (CREATE TABLE AS and INSERT) to copy data between Oracle databases. In general, the COPY command was designed to be used for copying data between Oracle and non-Oracle databases.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |