COPY Parameters
The following are parameters for the copy statement:
tablename
Specifies an existing table from which data is read or to which data is written
columnname
Specifies the column from which data is read or to which data is written
format
Specifies the format in which a value is stored in the file
filename
Specifies the file from which data is read or to which data is written
type
(Optional) Specifies the file type: text, binary, or variable
Unformatted Copying
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 an unformatted copy. For example, to copy the entire "employee" table into the file "emp_name", issue the following statement:
copy table employee () into 'emp_name';
You must include the parentheses in the statement, even though no columns are listed. The resulting binary file contains data stored in column binary formats. To load data from a file that was created by an unformatted COPY INTO, use an unformatted COPY FROM.
Formatted Copying
Formatted copying allows the type, number, and order of columns in the data file to differ from the table. By specifying a list of columns and their types in the COPY statement, you instruct Ingres to perform a formatted copy. The COPY statement list specifies the order and type of columns in the data file. Ingres uses the column names in the list to match up file data with the corresponding columns in the table.
For human readable text data files, the COPY list formats will almost always be a character type: char, c, text, or less commonly varchar or byte. The COPY statement converts (character) file data into table data types for COPY FROM, or the reverse for COPY INTO. The COPY list may contain other types as well, such as integer or decimal, but these are binary types for special programming situations; they are not human readable types. COPY also supports a "dummy" type, used to skip input data (FROM) or insert fixed output text (INTO).
Formatted copies can also specify that the character set of the data be changed from the national character set of the installation to UTF8. UTF8 is the recommended character set for portability because it can represent all of the characters used in the database. If the CHARSET option is not used and there are columns that contain UTF8 data, that data will be transliterated to the installation character set when written to file. If the UTF8 data was sourced from the installation character set then all the data will be able to be represented in the national character set. If this is not the case, and unrepresentable characters are present then, by default, a substitution error will occur or a substitution character used, if configured.
If some table columns are not listed in the COPY list for a COPY FROM, those columns are defaulted. (If they are defined in the table as NOT DEFAULT, an error occurs.) If some table columns are not listed for a COPY INTO, those table columns simply do not appear in the output data file.
The order of columns in the table need not match the order in the data file. Remember that the order of columns in the COPY list reflects the order in the data file, not the order in the table. Additionally, a table column may be named more than once. (For COPY FROM, if a column is named multiple times, the last occurrence in the COPY list is the one that is stored into the table. Earlier occurrences undergo format conversion, but the result is discarded.)
Special restriction: If the table includes one or more LONG columns (such as long varchar or long byte), columns cannot be reordered across any LONG column. For example, if the table contains (int a, int b, long varchar c), a COPY statement could use the order (b,a,c); but a COPY statement asking for (a,c,b) would be illegal (you cannot move column b to occur after the LONG column c).
The values in the data file can be fixed-length, or variable-length. Values can optionally be ended with a delimiter; the delimiter is specified in the COPY list. COPY can also process a special case of delimited values, such as the comma separated values (CSV) delimiting form.
Note: If II_DECIMAL is set to comma, be sure that when SQL syntax requires a comma (such as a fixed-length COPY type), that the comma is followed by a space. For example:
COPY TABLE t (col1=c20, col2=c30, d0=nl) INTO ‘t.out’:
Bulk Copying
To improve performance when loading data from a file into a table, you can use a bulk copy. The requirements for performing a bulk copy are:
• The table is not journaled
• The table has no secondary indexes
• The table is empty and occupies fewer than 18 pages if the table is other than heap
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.
Data File Format and Table Format
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, you must specify the column names in the order you want the values 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, you must sequence the table columns according to the order of fields in the data file.