Sybase 12.4.2 manual 186

Bulk loading data using the LOAD TABLE statement

LOAD TABLE adds rows

Simple LOAD TABLE Example

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-delimitercauses 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-specin 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.

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.

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’

186