Sybase 12.4.2 User Manual

CHAPTER 5 Moving Data In and Out of Databases

201

29 3 28 1376 3

200 4 13 119 4

59 5 9 4 5

(5 rows affected)

To ensure that the data from the second two columns is inserted into the same
rows as the first two columns, you must specify the row number in the

START

ROW ID

option on the

INSERT

command for the next two columns.

Using the FILLER
Option

The

FILLER

option tells Adaptive Server IQ which columns in the input file

to skip. This

LOAD TABLE

statement inserts NULLs into the second two

columns, because those columns are skipped. Note that these columns must
allow NULLs in order for this statement to work.

Example 3

For this next Windows NT example, assume the

partsupp

table has two

columns,

ps_partkey

and

ps_availqty

, and that

partsupp

is not part of any join

index.

The data for

ps_value

is calculated from

ps_availqty

so the

ps_availqty

column

must already contain data. Therefore, to insert data into the

partsupp

table, do

two inserts: one for

ps_availqty

and

ps_partkey

and then one for

ps_value

First, insert the data for

partsupp

directly from an ASCII file named tt.t.

LOAD TABLE partsupp

(ps_partkey ASCII(6),

ps_availqty ASCII(6),

FILLER(2))

FROM ’C:\\iq\\archive\\mill1.txt’

SELECT *, rowid(partsupp) FROM partsupp

ps_partkey ps_suppkey ps_availqty ps_value rowid(partsupp)

---------- ---------- ----------- -------- ---------------

213 NULL 190 NULL 1

24 NULL 215 NULL 2

(2 rows affected)

Next select the

ps_availqty

and do an 80% calculation. In this case you must use

INSERT

command to insert the results of a

SELECT

statement.

INSERT INTO partsupp(ps_value)

START ROW ID 1

SELECT ps_availqty * 0.80 FROM partsupp

SELECT *, rowid(partsupp) FROM partsupp

ps_partkey ps_suppkey ps_availqty ps_value rowid(partsupp)

---------- ---------- ----------- -------- ---------------