|
|
|
To copy all rows of a table to a file using the order and format of the columns in the table, omit the column list from the copy statement. This operation is referred to as a binary copy.
For example, to copy the entire employee table into the file, emp_name, issue the following statement:
copy table employee () into 'emp_name';
Parentheses must be included in the statement, even though no columns are listed. The resulting file contains data stored in proprietary binary formats. To load data from a file that was created by a binary copy (copy into), use a binary copy (copy from).
VMS: Bulk copy always creates a binary file.
|
|
|
To improve performance when loading data from a file into a table, use a bulk copy. The requirements for performing a bulk copy are:
If the DBMS Server determines that all these requirements are met, the data is loading using bulk copy. If the requirements are not met, data is loaded using a less rapid technique. For detailed information about bulk copying, see the Database Administrator Guide.
|
|
|
To specify the estimated number of rows to be copied from a file to a table during a bulk copy operation, use the row_estimate option. The DBMS Server uses the specified value to allocate memory for sorting rows before inserting them into the table. An accurate estimate can enhance the performance of the copy operation.
The estimated number of rows must be no less than 0 and no greater than 2,147,483,647. If this parameter is omitted, the default value is 0, in which case the DBMS Server makes its own estimates for disk and memory requirements.
|
|
|
Table columns need not be the same data type or length as their corresponding entries in the data file. For example, numeric data from a table can be stored in char(0) or varchar(0) fields in a data file. The copy statement converts data types as necessary. When converting data types (except character to character), copy checks for overflow. When converting from character to character, copy pads character strings with blanks or nulls, or truncates strings from the right, as necessary.
When copying from a table to a file, specify the column names in the order the values are to be written to the file. The order of the columns in the data file can be different from the order of columns in the table. When copying from a file to a table, specify the table columns in sequence, according to the order of the fields in the data file.
Note: If II_DECIMAL is set to comma, be sure that when SQL syntax requires a comma (such as list of table columns or SQL functions with several parameters), that the comma is followed by a space. For example:
select col1, ifnull(col2, 0), left(col4, 22) from ti: