Actian X 11.2 | Error Handling with the COPY Statement

Database Administrator Guide > Database Administrator Guide > Populating Tables > Successful Use of the COPY Statement > Error Handling with the COPY Statement

Was this helpful?

Error Handling with the COPY Statement

When using the COPY statement, use the various options on the WITH clause to control how invalid data is handled.

Stop or Continue the Copy

Use the WITH ON_ERROR clause to stop or continue copying the data when an error occurs. In the following example, the copy continues after finding an error:

COPY TABLE personnel
    (name= CHAR(0),
    dept = CHAR(0)nl)
    FROM 'pers.data'
    WITH ON_ERROR = CONTINUE;

The default is to terminate at the first error.

Stop the Copy After a Specified Number of Errors

To stop the copy after a certain number of errors, specify an error count with the ERROR_COUNT=n clause. For example:

COPY TABLE personnel
    (name = CHAR(0),
    dept = CHAR(0)nl)
    FROM 'pers.data'
    WITH ERROR_COUNT = 10;

The default ERROR_COUNT is 1.

This clause is not meaningful when used with the ON_ERROR=CONTINUE clause. See the Error_ Count Option for the COPY statement in the SQL Reference Guide.

Roll Back Rows

By default, copying data stops after finding an error. If you do not want to back out the rows already copied, specify with WITH ROLLBACK=DISABLED. For example:

COPY TABLE personnel
    (name = CHAR(0),
    dept = CHAR(0)nl)
    FROM 'pers.data'
    WITH ROLLBACK = DISABLED;

Use the WITH ROLLBACK clause on the COPY FROM statement only. Rows are never backed out of the copy file if copy into is terminated. For more information, see the SQL Reference Guide.

Log Errors During Copy

Use the WITH LOG clause to put invalid rows into a log file for future analysis. The following query is terminated after ten errors, and these errors are placed in a log file named badrows.data:

COPY TABLE personnel
    (name = CHAR(0),
    dept = CHAR(0)NL)
    FROM 'pers.data'
    WITH ERROR_COUNT = 10,
    LOG = 'badrows.data';

Note: COPY does not write duplicate rows to the log file.

Continue the Copy and Log Errors

By using both LOG and ON_ERROR = CONTINUE in the clause, put invalid rows in a log file and continue to process valid ones. Correct the rows in the log file and load them into the database. For example:

copy table personnel
    (name = char(0),
    salary = char(0)nl)
    from 'pers.data'
    with on_error = continue,
    log = 'badrows.data';

Last modified date: 04/03/2024