Filter
Top Products
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
an
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)
---------- ---------- ----------- -------- ---------------