With Clause Options for COPY
Valid WITH clause options for the COPY statement are as follows:
ON_ERROR = TERMINATE | CONTINUE
Directs COPY to continue after encountering conversion errors.
To direct copy to continue until a specified number of conversion errors have occurred, specify the ERROR_COUNT option instead.
By default, COPY terminates when an error occurs while converting between table format and file format
When ON_ERROR is set to CONTINUE, COPY displays a warning whenever a conversion error occurs, skips the row that caused the error, and continues processing the remaining rows. At the end of the processing, COPY displays a message that indicates how many warnings were issued and how many rows were successfully copied.
Setting ON_ERROR to CONTINUE does not affect how COPY responds to errors other than conversion errors. Any other error, such as an error writing the file, terminates the COPY operation.
ERROR_COUNT = n
Specifies how many errors can occur before processing terminates.
Default: 1.
If ON_ERROR is set to CONTINUE, setting ERROR_COUNT has no effect.
Note: ON_ERROR and ERROR_COUNT cannot be in the same statement.
LOG = 'filename'
Stores to the specified file any rows that COPY cannot process. This option can be used only if ON_ERROR CONTINUE is specified. For COPY INTO, the logged rows are in database (binary) format; for COPY FROM, the logged rows are in file format.
Logging works as follows:
COPY opens the log file prior to the start of data transfer. If it cannot open the log file, COPY halts. If an error occurs when writing to the log file, COPY issues a warning, but continues. If the specified log file already exists, it is overwritten with the new values (or truncated if the copy operation encounters no bad rows).
COMPRESSION
Creates LZH compressed data files for COPY INTO.
SKIP = n
Directs COPY to skip n rows (where n is a positive integer) from the beginning of the file being read. This is useful when the file contains, for example, a header record which is not to be written to the table.
Default: 0 (skip no rows)
STATISTICS
For COPY...FROM, creates statistics on the table just loaded. Histograms are built for all columns in the loaded table.
FILESIZE=size
Splits the output file into multiple parts, where size is the number of megabytes per part.
DOUBLE_QUOTE_ONLY
Enables the quoting behavior for the previous versions of CSV and SSV copy formats, (double quote character (“)). If the CBF parameter copy_dbl_quote_only is set to ON, this is the default option.
NODOUBLE_QUOTE_ONLY
Enables quoting using both single (‘) and double quote (“) characters for CSV and SSV copy formats. If the CBF parameter copy_dbl_quote_only is set to OFF, this is the default option.
Last modified date: 03/21/2024