Considerations for Copying Formatted Large Objects
A column with large objects is specified for copying with the formats:
long varchar(0)
long byte(0)
long nvarchar(0)
Note: The field width specifier must be zero.
To handle the large size, copy deals with these data types in a similar manner as the data handlers: the data is broken up into segments for copying to a data file.
Each segment consists of the length, followed by a space delimiter, followed by the data. There is no space following a data segment (because copy knows how many bytes of data to read).
The basic structure of a formatted segment is:
integer = length of segment in bytes
space = delimiter
char|byte(len) = data
The last segment, or an empty object, is denoted by a zero length, followed by its space delimiter:
0 = length of segment
space = delimiter
Thus, the data is segmented as:
length1 segment1 segment1length2 segment2...lengthn segmentn0
^ ^ ^ ^ ^ ^
space space space space space space
The segments of the long nvarchar are UTF-8 transformation of Unicode values.
For formatted copies on large object data that contain nulls, the WITH NULL clause must be specified with a value.
Example: Copying Formatted Large Objects
Consider the sample table, big_table, that was created with the following CREATE TABLE statement:
CREATE TABLE big_table
object_id INTEGER,
big_col LONG VARCHAR);
This table can be copied to the big_file file with the following COPY statement:
COPY TABLE big_table (object_id INTEGER, big_col LONG VARCHAR) INTO 'big_file';