|
|
|
When using the copy statement, you should avoid common problems and learn to use the statement correctly. Specifically, you should understand how to do the following:
|
|
|
When you use the copy statement, the data being copied is not checked for integrity errors.
To check the integrity of your data before using the copy statement, follow these steps:
create integrity on personnel
is name like '\[A-Z\]%' escape '\';
If the search condition is not true for every row in the table, an error message is returned and the integrity constraint is rejected.
select name from personnel
where name not like '\[A-Z\]%' escape '\';
As an additional check that the information was copied correctly, apply the integrity constraint after copying the table.
For more information on integrity checking and integrity constraints, see the chapter "Ensuring Data Integrity."
|
|
|
When using the copy from statement, the following problems in the copy file are the most frequent causes for error messages:
|
|
|
If you try to load invalid data into a field, the row is rejected.
For example, the following record is rejected because February has only twenty-eight or twenty-nine days:
559-58-2543,31-feb-1998,Weir,100000.00,Executive
|
|
|
If the widths of fixed-length fields are not correct, the copy statement can try to include data in a field that it cannot convert to the appropriate format.
For example, you receive an error message if you try to copy this row:
554-39-2699 01-oct-1998 Quinn 28000.00 Assistant
with the following copy statement:
copy table personnel (ssno = char(20),
birthdate = char(11),
name = char(11),
salary = char(9),
title = char(0)nl)
from 'pers.data';
Because you specified char(20), or 20-character positions, for the ssno field, the copy statement includes part of the birth date in the value for the ssno field. When the copy statement tries to read the birth date, it reads "998 Quinn 2" which is not a valid date if birth date is defined as a date field; if defined as a char field, you get an "unexpected EOF" error.
|
|
|
When using fixed-length specifications in the copy statement, you must account for the "nl" (newline) character at the end of the record.
For example, you receive an error message if you try to copy these records:
554-39-2699 01-oct-1998 Quinn 28000.00 Programmer
335-12-1452 23-jun-1998 Smith 79000.00 Sr Analyst
with the following copy statement:
copy table personnel (ssno = char(12),
birthdate = char(12),
name = char(6),
salary = char(9),
title = char(10))
from 'pers.data';
The format specified for the title field is char(10), which does not account for the newline character. The newline characters are converted to blanks, and the extra characters force the copy statement to begin reading a third record that ends abnormally with an unexpected end of file. Use char(10)nl to avoid this problem.
|
|
|
If you omit delimiters between fields in the data file, the record is rejected.
For example, the first record below has no delimiter between the employee's name and her salary:
123-45-6789,01-jan-1998,Garcia33000.00,Programmer246-80-1357,02-jan-1998,Smith,43000.00,Coder
If you try to copy these records with the following copy statement, you receive an error message because the copy statement attempts to read "Programmer" into the "salary" field:
copy table personnel
(ssno = char(0),
birthdate = char(0),
name = char(0),
salary = char(0),
title = char(0)nl)
from 'pers.data';
|
|
|
Be careful not to include too many delimiters in the data file. This mistake frequently occurs when you use the comma as a delimiter and it also appears in the data.
For example, in the first row, the salary value contains a comma:
123-45-6789,01-jan-1998,Garcia,33,000.00,Programmer
246-80-1357,02-jan-1998,Smith,43000.00,Coder
If you try to copy these records with the following copy statement, you receive an error message:
copy table personnel
(ssno = char(0),
birthdate = char(0),
name = char(0),
salary = char(0),
title = char(0))
from 'pers.data';
You receive an error because the copy statement reads:
It attempts to read "246-80-1357" as the birthdate, which produces the error.
If you specified "title = char(0)nl", the copy statement still reads "33" as the salary, but it reads "000.00,Programmer" as the title. This is because it looks for a newline rather than a delimiter at the end of the title. It reads the next row correctly. Although an error message is not generated, the title field for one row is incorrect.
|
|
|
When using the copy statement, use the various options on the with clause to control how invalid data is handled.
|
|
|
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.
|
|
|
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.
|
|
|
By default, copying data stops after finding an error. If you do not want to back out the rows already copied, specify 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 with the copy from statement only. Rows are never backed out of the copy file if copy into is terminated. For additional information, see the SQL Reference Guide.
|
|
|
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';
|
|
|
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';
|
|
|
Follow these tips if you have trouble loading your data into the designated tables:
If you are not able to load data from binary files: