The copy statement copies the contents of a table to a data file (copy into) or copies the contents of a file to a table (copy from). For more information on the copy statement, see Populating Tables in the Ingres Database Administrator Guide.

This statement has the following syntax:

This statement has the following parameters:

To copy all rows of a table to a file with no conversion or formatting, 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:

Parentheses must be included 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 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).

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 (see Delimiters in the Data File); the delimiter is specified in the copy list. copy can also process a special case of delimited values, the comma separated values (CSV) delimiting form.

To improve performance when loading data from a file into a table, use a bulk copy. Bulk copy is possible for either formatted or unformatted copies.

The requirements for performing a bulk copy are:

If the DBMS Server determines that all these requirements are met, the data is loaded 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 Ingres 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 with row_estimate (see Row_estimate).

The following sections describe how to specify the data file format for table columns. The format specifies how each is written and delimited in the data file.

The character formats are the ones most commonly used to read and write ordinary text (human-readable) data files.

The basic character formats are byte, c, char, and text. Each has a variable-length form and a fixed-length form. The variable-length forms are byte(0), c0, char(0), and text(0). The fixed-length forms are byte(n), Cn, char(n), and text(n). An optional delim may follow to specify a delimiter.

The subtle differences between the various character formats are described in Copy Format Details.

The byte varying, long byte, long varchar, long nvarchar, nvarchar(n), and varchar formats are "counted" formats: each data file value is preceded by a character count. The character count defines the length of the data value; the actual field length as defined by a fixed-length specifier or a delimiter may be larger. On input (copy from), extra field characters beyond those included by the embedded character count are ignored. On output (copy into), any extra field length after the actual value is filled with padding, as defined by the specific format.

The fixed-length forms are byte varying(n), nvarchar(n), and varchar(n). The variable-length forms are byte varying(0), long byte(0), long varchar(0), long nvarchar(0), and varchar(0). An optional delim may follow to specify a delimiter.

For all fixed-length counted formats: the field length N does not include the preceding length specifier. For example, a varchar(1) field takes 6 bytes. When reading data (copy from), if the character count found in the data is larger than the defined length, a runtime conversion warning is issued and the row is not loaded.

These counted formats are the only ones that can be used with long table columns. The two long formats can only be used with long byte or long varchar table columns. See Copy Format Details.

The D (dummy) format describes a data file column that does not map to any table column. On input (copy from), a D format column describes file data to be skipped and discarded. On output (copy into), a D format column describes constant data to be sent to the data file.

The column name given for any dummy column is not matched to any table column. The Dn form for copy into uses the column name as the value to output; all other uses of the dummy format ignore the column name completely.

The Unicode formats are nchar, nvarchar, and long nvarchar and they can only be used with nchar or nvarchar table columns. Fixed-length forms are nchar(n) and nvarchar(n). Variable-length forms are nchar(0), nvarchar(0), and long nvarchar(0).

Fixed-length nchar(n) and nvarchar(n) formats read and write using the two-byte UCS-2 encoding. The variable-length nchar(0), nvarchar(0), and long nvarchar(0) forms read and write using the variable-length UTF8 encoding.

The field length n for nchar(n) and nvarchar(n) should be specified as character lengths, not byte (octet) lengths. However, the embedded length specifier used by the nchar(0) and nvarchar(0) formats should give the number of bytes, not characters. (The reason is that nchar(0) and nvarchar(0) use the UTF8 encoding, which encodes Unicode code points into a variable number of bytes. Copy needs the byte count to know how many bytes to read and decode from UTF8.)

The formatted copy statement supports binary formats that match the binary types used to store data in tables. These are the boolean, date, decimal, float, integer, and money formats (and size variants such as bigint, smallint, real, and so on). Most data files are text, not binary, so these binary formats are not often needed.

This section describes specifying the format of fields in the data file. When specifying data file formats for copy into, be aware of the following points:

The following table explains the details for the various copy list formats. Unless otherwise noted, all non-binary formats can be followed by an optional delim to specify a delimiter (see Delimiters in the Data File).

For user-defined data types (UDTs), use char or varchar.

Delimiters are characters in the data file that separate fields and mark the end of records. Valid delimiters are listed in the following table:

When a single character is specified as the delimiter, enclose that character in quotes. If the data type specification is C or D, the quotes must enclose the entire format. For example, 'd0%' specifies a dummy column delimited by a percent sign (%). If the data type specification uses parentheses around the length, quote only the delimiter. For example, char(0)'%' specifies a char field delimited by a percent sign.

Be careful using the sp (space) or null delimiters, especially with copy from. Spaces or nulls are used as padding characters by many of the copy formats. If a pad character is improperly treated as a delimiter, the copy from will get out of sync with the input, eventually producing an error. When designing a data file format, use delimiters that will not appear in the data or padding, or use CSV or SSV forms.

The CSV and SSV delimiters allow copy to read and write files that contain comma separated values (CSV).

The rules for a CSV-delimited field are:

The SSV delimiter works exactly the same as the CSV delimiter, with semicolon in place of comma.

CSV and SSV delimiters are only allowed with byte(0), C0, char(0), and text(0). They are not allowed with the “counted” formats (varchar(0) and so on); the count defines the value exactly and there is no need for quoting. (If delimiting is desired, use the comma or nl delimiters on counted formats.)

Copy from: Some CSV file variants use quote escaping (\") instead of quote doubling ("") to indicate a quote inside a quoted string. The C format handles\- escaping, so use the C0CSV format and delimiter to handle this type of file. (CSV with copy into always writes quote doubling—never quote escaping—when needed.)

The with null clause allows the data file to contain null data. There are two methods of indicating a null: an indicator byte (no value given), or a special user-defined marker value. The indicator byte method does not depend on a particular marker value, but it is a binary form not suited to human readable text files. Also, the indicator byte method is not available for variable-length data fields. The user-defined marker value method works with any file format, but depends on a user chosen value that must not otherwise appear in the data.

When copying data from a table to a file, the with null (value) clause directs copy to put the specified value in the file when a null is detected in the corresponding column. If a null is detected and there is no with null clause, a runtime error occurs, and aborts the copy statement.

When copying data from a file to a table, the with null (value) clause specifies a marker value to be interpreted as a null. When copy encounters this value in the file, it writes a null to the corresponding table column. The table column must be nullable; if it is not, a runtime error occurs, and aborts the copy statement.

To prevent conflicts between valid data and null entries, choose a value that does not occur as part of the data in your table. The value chosen to represent nulls must be compatible with the format of the field in the file: character formats require quoted values, and binary numeric formats require unquoted numeric values.

For example, this example of a value is incorrect:

because the value specified for nulls (numeric zero) conflicts with the character data type of the field. However, this example is correct:

because the null value is character data, specified in quotes, and does not conflict with the data type of the field. Do not use the keyword null, quoted or unquoted, for a numeric format.

When copying from a table to a file, be sure that the specified field format is at least as large as the value specified for the with null clause. If the column format is too small, the DBMS Server truncates the null value written to the data file to fit the specified format.

For example, in the following statement the string, 'NULL,' is truncated to 'N' because the format is incorrectly specified as one character:

The correct version specifies a 4-character format for the column.

If with null is specified but value is omitted, copy uses a trailing indicator byte in the file to determine whether a file value is a null. Copy into writes a zero trailing byte if the value is not null; it writes a nonzero trailing byte to indicate a null. (The value written prior to the indicator byte is undefined if the indicator shows null.) Copy from reads and interprets a trailing indicator byte in the same manner, zero for not null and nonzero for null. Indicator bytes are not allowed for variable-length formats such as char(0); variable-length formats must specify a value clause.

Filename must be enclosed in single quotation marks; the file specification can include a directory/path name. For copy into, if the file does not exist, copy creates the file.

UNIX: For copy into, if the file already exists, copy overwrites it.

File type can be specified using the optional type parameter. Type must be either T for text, or B for binary.

The traditional Windows newline indicator is a CR-LF pair (carriage return / linefeed). The newline indicator on other operating systems (such as UNIX) is a single linefeed with no carriage return. Windows uses the file type to control translation between Windows and UNIX style newline indicators, as well as control-Z translation.

A file in binary type mode reads or writes the data exactly as is, with no translation. A file in text type mode translates a single LF to CR-LF when writing. When reading a file in text mode, CR-LF pairs are read as single LF's, and if a control-Z occurs in the data file, end-of-file is returned and Windows stops reading data from that file.

By default, Ingres uses text mode for copy into and copy from only if all of the listed field formats are character types (c, char, text, varchar, or dummy). Otherwise, binary mode is used.

The binary-copy forms (copy () from or copy () into) use binary mode.

Copy from recognizes CR-LF as a newline (nl) delimiter even if the input file is read in binary type mode. (This is true on non-Windows systems too, so that data files that were created by Windows applications can be read.)

For situations where the default file type choice is inappropriate, the file type can be specified explicitly. For example, if copy into is creating a file to be read on a UNIX system, a file type of B (Binary) is appropriate. The resulting file will contain UNIX-style newlines (single linefeeds) instead of Windows-style newlines.

Valid with clause options for the copy statement are as follows:

To use the copy statement, one of the following must apply:

Copy cannot be used to add data to a view, index, or system catalog.

When copying data into a table, copy ignores any integrity constraints defined (using the create integrity statement (see Create Integrity Statement)) against the table.

When copying data into a table, copy ignores ANSI/ISO Entry SQL-92 check and referential constraints (defined using the create table (see Create Table Statement) and alter table statements), but does not ignore unique (and primary key) constraints.

The copy statement does not fire any rules defined against the table.

Values cannot be assigned to SYSTEM_MAINTAINED logical key columns. The DBMS Server assigns values when copying from a data file to a table. This occurs even if the logical key column is being implicitly loaded using an unformatted copy; the values in the data file are ignored, and new values assigned by the DBMS Server.

Copy treats a present-but-empty field in the data file as:

For example, if you load a data file (copy from) that contains a record with one empty value into a table with a mandatory (not default) integer field, the load of the one record does not fail due to the missing value, but succeeds and loads a 0. You do get the error for the missing value if the column is omitted from the copy list. Note that if the table has a column defined with a default value and the field in the data file is present but empty, copy loads the default as listed above. If you omit the column from the copy list then you will get the proper default value.

The following examples illustrate the correct use of the copy statement: