Filter
Top Products
CHAPTER 5 Moving Data In and Out of Databases
185
You cannot use this option if any input fields contain binary data. With this
option, a row terminator causes any missing fields to be set to NULL. All rows
must have the same row delimiters, and it must be distinct from all column
delimiters. The row and field delimiter strings cannot be an initial subset of
each other. For example, you cannot specify “*” as a field delimiter and “*#”
as the row delimiter, but you could specify “#” as the field delimiter with that
row delimiter.
If a row is missing its delimiters, Adaptive Server IQ returns an error and rolls
back the entire load transaction. The only exception is the final record of a file
where it rolls back that row and returns a warning message.
On Windows NT, a row delimiter is usually indicated by the newline character
followed by the carriage return character. You may need to specify this as the
delimiter-string for either this option or FILLER.
Example
The following Windows NT example sets the column delimiter for the
l_orderkey column to tab, and the row delimiter to newline (\x0a) followed by
carriage return (\x0d):
LOAD TABLE mm
(l_orderkey ’\x09’,
l_quantity ASCII(4),
FILLER(6),
l_shipdate DATE(’YYYY/MM/DD’))
FROM ’C:\\iq\\archive\\mill.txt’
ROW DELIMITED BY ’\x0a\x0d’
SKIP option
Lets you define a number of rows to skip at the beginning of
the input file(s) for this load. The default is 0. This option works in conjunction
with the
LIMIT option, and takes precedence over it.
In this UNIX example, Adaptive Server IQ reads 9,000 rows from the input
file, skips the first 5,000, and loads the next 4,000. If there are only 8,000 rows
in the input file, then only 3,000 rows are loaded.
LOAD TABLE lineitem(
l_shipmode ASCII(15),
l_quantity ASCII(8),
FILLER(30))
FROM ’/d1/MILL1/tt.t’
BLOCK FACTOR 1000
LIMIT 4000
SKIP 5000
PREVIEW ON