Column indexes that are not included in the initial partial-width insert, and therefore do not already contain data, must allow NULLs. Sybase IQ inserts NULLs into these column indexes. If they do not allow NULLs, the insert fails.
When doing partial-width inserts, follow these steps:
For the first partial-width insert for each set of rows, do not specify START ROW ID. Sybase IQ automatically knows what the next available row is for this insert.
For the second and any subsequent partial-width inserts for the same set of rows, use the START ROW ID option to specify the row where the insert started. This number is the record number at the beginning of the insert message log, as in this example:
In table 'department', the full width insert of 3 columns will begin at record 1.
You can also use the ROWID function to display the row ID, as in the following query:
SELECT *, ROWID(table_name) FROM table_name
The UNIX example below shows an incorrect insertion of four columns from the file tt.t into the indexes on the lineitem table. It inserts the first two columns with one LOAD TABLE statement and the second two columns with another LOAD TABLE statement, but does not use the START ROW ID option to align the additional columns.
LOAD TABLE lineitem
(l_partkey ASCII(4),
l_suppkey ASCII(4),
FILLER(13))
FROM '/d1/MILL1/tt.t'
PREVIEW ON
NOTIFY 1000
LOAD TABLE lineitem
(FILLER(8),
l_quantity ASCII(6),
l_orderkey ASCII(6),
FILLER(1))
FROM '/d1/MILL1/tt.t'
PREVIEW ON
NOTIFY 1000
The result of the SELECT statement below shows that 10 rows are stored instead of the correct number of 5.
SELECT *, rowid(lineitem) FROM lineitem
l_orderkey l_partkey l_suppkey l_quantity rowid(lineitem) ---------- --------- --------- ------------ --------------- NULL 1 12 NULL 1 NULL 2 37 NULL 2 NULL 3 28 NULL 3 NULL 4 13 NULL 4 NULL 5 9 NULL 5 190 NULL NULL 19 6 215 NULL NULL 2127 7 29 NULL NULL 1376 8 200 NULL NULL 119 9 59 NULL NULL 4 10 (10 rows affected)
The following example shows the correct way to do this operation. Note the START ROW ID option in the second LOAD TABLE statement.
LOAD TABLE lineitem
(l_partkey ASCII(4),
l_suppkey ASCII(4),
FILLER(13))
FROM '/d1/MILL1/tt.t'
PREVIEW ON
NOTIFY 1000
SELECT *, rowid(lineitem) FROM lineitem
l_orderkey l_partkey l_suppkey l_quantity rowid(lineitem) ---------- --------- --------- ---------- --------------- NULL 1 12 NULL 1 NULL 2 37 NULL 2 NULL 3 28 NULL 3 NULL 4 13 NULL 4 NULL 5 9 NULL 5 (5 rows affected)
LOAD TABLE lineitem
(FILLER(8),
l_quantity ASCII(6),
l_orderkey ASCII(6),
FILLER(1))
FROM '/d1/MILL1/tt.t'
PREVIEW ON
NOTIFY 1000
START ROW ID 1
SELECT *, rowid(lineitem) FROM lineitem
l_orderkey l_partkey l_suppkey l_quantity rowid(lineitem) ---------- ---------- ---------- ----------- --------------- 190 1 12 19 1 215 2 37 2127 2 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.
The FILLER option tells Sybase 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.
For this next Windows 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) ---------- ---------- ----------- -------- --------------- 213 NULL 190 152.00 1 24 NULL 215 172.00 2 (2 rows affected)
If you later load data from another file into ps_partkey and ps_availqty, insertions begin correctly at the next row, as shown below.
LOAD TABLE partsupp
(ps_partkey ASCII(6),
ps_availqty ASCII(6),
FILLER(2))
FROM 'C:\\iq\\archive\\mill2.txt'
SELECT *, rowid(partsupp) FROM partsupp
ps_partkey ps_suppkey ps_availqty ps_value rowid(partsupp) ---------- ---------- ----------- -------- --------------- 213 NULL 190 152.00 1 24 NULL 215 172.00 2 28 NULL 490 NULL 3 211 NULL 15 NULL 4 (4 rows affected)
To calculate and insert the values for ps_value, you need to repeat the INSERT statement shown earlier in this example, changing the START ROW ID value to the new row number, 3.
Given the possibility of errors if you do a partial-width insert incorrectly, it is a good idea to preview these inserts. The PREVIEW load option lets you see the layout of input in the destination table. This option is available in LOAD TABLE, but not in the INSERT command.
