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 Actian X to perform a formatted copy. The COPY statement list specifies the order and type of columns in the data file. Actian X uses the column names in the list to match the data in the file 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 can 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 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. This behavior can be controlled with the SQL command SET [NO]SUBSTITUTION_CHARACTER.

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 can 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 (some require it) (see Delimiters in the Data File); 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) form.