Filter
Top Products
Bulk loading data using the LOAD TABLE statement
186
START ROW ID option Specifies the id number of a row in the table
where insertions should begin. This option is used for partial-width insertions,
which insert into a subset of the columns in the table. If you are inserting data
into an existing row, you must define the format of each input column with a
column-spec, and use
START ROW ID to identify the row where you want to
insert it. The default is 0, which causes data to be inserted in a new row
wherever there is space in the table. Be sure to read “Partial-width insertions”
before using this option and performing partial-width inserts.
UNLOAD FORMAT option Specifies that the data in the input file is in the
format produced by the
UNLOAD command in Adaptive Server IQ 11.5.1,
specifically for upgrading to Adaptive Server IQ 12.x. This format places
certain restrictions on other load options you specify:
• The format in the column specifications must be
BINARY, the default.
Specifying
ASCII, PREFIX, FILLER, or string-delimiter causes an error.
• You must not use the load options
DELIMITED BY and ROW DELIMITED
BY
.
• To allow NULLs in the data you must specify
BINARY WITH NULL BYTE
in the column specification. You cannot include
NULL in the column-spec
in any other way.
• For the sake of consistency with the data being loaded, you can specify
BINARY WITH NULL BYTE even when loading into a table column that
does not allow NULLs (as specified in
CREATE TABLE or ALTER TABLE).
However, if you try to load any data into a column that does not allow
NULLs, you receive an error.
See the Adaptive Server IQ Installation and Configuration Guide for more
information on upgrading.
LOAD TABLE adds
rows
The LOAD TABLE statement appends the contents of the file to the existing
rows of the table; it does not replace the existing rows in the table, unless you
specify the
START ROW ID load option. See “Partial-width insertions” for
examples of how you use this option to insert data into existing rows.
If you want to empty out an existing table and reload it, you can use the
TRUNCATE TABLE statement to remove all the rows from a table.
Simple LOAD TABLE
Example
The following statement loads the data from the file dept.txt into all columns
of the
department table. This example assumes that no explicit data conversion
is needed, and that the width of input columns matches the width of columns
in the
department table.
LOAD TABLE department
FROM ’dept.txt’