Formatted Copying

Formatted copying provides a flexible means of copying tables.

Column Name and Format Specifications

When using the copy statement to do formatted copying, specify the column name and the format in which that column's data is to be copied, as follows:

column_name = format [null-clause]

The column_name specifies the column from which data is read or to which data is written. The name in the copy target must be the same as in the copy source; you cannot change column names in the copy statement.

The format specifies the storage format in which the column values are stored and delimited. The storage format is based on the data type. The copy statement can copy all data types except logical keys.

The column names and their formats must be separated by commas, and the list must be in parentheses.

Summary of Data Types and Storage Formats

A summary of data types and their storage format characteristics is given in the table below. For detailed information on storage formats and data conversions of the various data types, see the SQL Reference Guide.

Class	Data Types	Description and Copy Notes
Character data	char	Fixed-length strings with blank padding at the end. char(0)[delim] copies the string without requiring a specified length, with a default or specified delimiter as an end of record. char(n) copies n = 1 to x characters. "x" represents the lesser of the maximum configured row size and 32,000.
Character data	varchar	Variable-length strings preceded by a length. varchar(0) copies the string and its stored length. varchar(n) copies n = 1 to x characters and its stored length, with null padding at the end. "x" represents the lesser of the maximum configured row size and 32,000.
Character data	long varchar	Stored in segments, and terminated by a zero length segment. Each segment is composed of an integer specifying the length of the segment, followed by a space and the specified number of characters. The end of the column data is specified through a termination, zero length segment (that is, an integer 0 followed by a space).
		The following example shows two data segments, followed by the termination zero length segment. The first segment is 5 characters long, the second segment is 10 characters long, and the termination segment is 0 character long. The maximum length of each segment is 32737. 5 abcde10 abcdefghij 0 (with a space after the terminating 0 character) (In this example, the effective data that was in the column is abcdeabcdefghij) If the long varchar column is nullable, specify the with null clause. An empty column is stored as an integer 0, followed by a space.
Unicode data	nchar	Fixed-length Unicode strings in UTF-8 format (padded with blanks if necessary).
Unicode data	nvarchar	Variable-length Unicode string in UTF-8 format preceded by a 5-character, right-justified length specifier.
Unicode data	long nvarchar	Stored in segments, and terminated by a zero length segment. Each segment is composed of an integer specifying the length of the segment, followed by a space and the specified number of Unicode characters in UTF-8 format. The end of the column data is specified through a termination, zero length segment (that is, an integer 0 followed by a space). The maximum length of each segment is 32727 bytes. Note: The "number" of Unicode characters in a segment will be less than 32767. For example, each UTF-16 character in Basic multilingual plane can occupy 1 to 3 bytes in UTF-8 format. If the long nvarchar column is nullable, specify the with null clause. An empty column is stored as an integer 0, followed by a space. The UTF-8 encoded long nvarchar data segments are similar to long varchar data segments. For an example of the encoded data segment, see the description for long varchar.
Binary data	byte	Fixed-length binary data with padding of zeroes at the end. byte(0)[delim] copies the data without requiring a specified length, with a default or specified delimiter as an end of record. byte(n) copies n= 1 to x bytes. "x" represents the lesser of the maximum configured row size and 32,000.
Binary data	byte varying	Variable-length binary data preceded by a length. byte varying(0) copies the data and its stored length. byte(n) copies n= 1 to x bytes and its stored length, with zero padding. "x" represents the lesser of the maximum configured row size and 32,000.
Binary data	long byte	Binary data stored in segments, and terminated by a zero length segment. Each segment is composed of an integer specifying the length of the segment, followed by a space and the specified number of characters. The end of the column data is specified through a termination, zero length segment (that is, an integer 0 followed by a space). The following example shows two data segments, followed by the termination zero length segment. The first segment is 5 characters long, the second segment is 10 characters long, and the termination segment is 0 character long. The maximum length of each segment is 32737. 5 abcde10 abcdefghij 0 (with a space after the terminating 0 character) (In this example, the effective data that was in the column is abcdeabcdefghij) If the long byte column is nullable, specify the with null clause. An empty column is stored as an integer 0 followed, by a space.
Numeric data	integer1	Integer of 1-byte length (-128 to +127).
Numeric data	smallint	Integer of 2-byte length (-32,768 to +32,767).
Numeric data	integer	Integer of 4-byte length (-2,147,483,648 to +2,147,483,647).
Numeric data	decimal	Fixed-point exact numeric data, up to 31 digits. Range depends on precision and scale. Default is (5,0): -99999 to +99999.
Numeric data	float4	Single precision floating point number of 4-byte length (7 digit precision), -1.0e+38 to +1.0e+38.
Numeric data	float	Double precision floating point number of 8-byte length (16 digit precision), -1.0e+38 to +1.0e+38).
Date/time data	ansidate	4-byte binary
Date/time data	time	8- or 10-byte binary
Date/time data	timestamp	12- or 14-byte binary
Date/time data	interval year to month	3-byte binary
Date/time data	interval day to second	12-byte binary
Abstract data types	ingresdate	Date of 12-byte length, 1-jan-1582 to 31-dec-2382 (for absolute dates) and -800 years to 800 years (for time intervals).
Abstract data types	money	Exact monetary data of 8-byte length, $-999,999,999,999.99 to $999,999,999,999.99
Copy statement only	d	Dummy field. d0delim on copy into, copies the delimiter into the (empty) field. d0[delim] on copy from, skips the data in the field, up to the default or specified delimiter. dn on copy into, copies the name of the column n times. On copy from, skips the field of n characters.
User-defined data types (UDTs)		Use char or varchar.

Copy Statement and Nulls

When you copy data from a table to a file or vice versa, the with null clause of the copy statement allows you to substitute a value for nulls.

When you use variable length data formats when copying, you must replace the null values with some string that represents nulls; for example:

copy table personnel (name=char(20),
    salary=char(0) with null ('N/A'),
    dummy=d0nl)
    into 'pers.data';

After executing this statement, the pers.data file contains "N/A" for each null salary.

With other data formats, you are not required to substitute a value for nulls. However, if you do not, your file contains unprintable characters.

When substituting a value for nulls, the value:

Must not be one that occurs in your data
Must be compatible with the format of the field in the file:
- Character formats require quoted values
- Numeric formats require unquoted numeric values

Do not use the word null if you are copying to a numeric format. The file does not accept an actual null character or the word "null" for numeric format.

Copy Data into a Formatted File

Use the following copy statement to copy table data into a formatted file:

copy [table] [schema.]tablename

([column_name = format [with null [(value)]]

{, column_name = format [with null[(value)]]}])

into 'output_filename' [standard-with-clauses]

One or more column names appear, with format specifications. The column names must be the same as those in the table. However, the order of the columns can be different from the order in which they are currently stored in the table (except as noted below). Also, the format does not have to be the same data type or length as their corresponding entries in the table. The data is copied with any column reorganization or format conversions being made as necessary.

Note: When copying from a table that includes long varchar or long byte columns, you must specify the columns in the order they appear in the table.

Two major categories of data that can be unloaded into files are fixed-length fields and variable-length fields.

Data with Fixed-Length Fields

Fixed-length fields can use implicit or explicit specification of the field length.

If you use the (0) notation for fixed-length character or byte data, the length is implicitly specified. For example, if you use char(0), character columns are copied into the file using the full length of the column.
Columns containing numeric data (such as integer or float data types) can be explicitly formatted using the -i or -f SQL option flags. For details on these flags, see the sql command description in the Command Reference Guide.
If you use a length specifier, the field length is explicit. For example, for char(n), the copy into statement stores exactly n characters. Excess characters are discarded and shorter columns are padded with blanks.
The varchar(n) format stores exactly n characters with a leading length indicator in ASCII format, discarding any excess characters. Shorter columns are padded with null bytes.

Data with Variable-Length Fields

Variable-length data items are written to a file by the copy statement with the formats:

varchar(0)
long varchar(0)
byte varying(0)
long byte(0)
nvarchar(0)
long nvarchar(0)

An ASCII length is written preceding the data. The length of the data copied corresponds to the number of characters or bytes in the column, not the width of the column specified in the create statement. Varchar(0) compresses the data, whereas char(0) does not.

Reload Formatted Data

Use the following form of the copy statement to reload a table from a file containing formatted data:

copy [table] [schema.]tablename

([column_name = format [with null [(value)]]

{, column_name = format [with null[(value)]]}])

from 'input_filename' [standard-with-clauses]

[bulk-copy-with-clauses]

The input file name can contain user-created data for reading in new data to a table, or a formatted file created by a copy into statement. You must specify the column names in sequence according to the order of the fields in the formatted file (that is, the same order in which they appeared in a copy into statement). The format does not have to be the same data type or length as their corresponding entries in the file. The target table can be empty or populated; in the latter case, the copy from operation merges the new data from the file with the existing table data. If the table characteristics allow, include bulk copy with clauses.