Partial-width insertion rules

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:

  1. 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.

  2. 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 'Departments', 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

Example 1

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)

Example 2

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.

Using the FILLER Option

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.

Example 3

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.

Previewing partial-width inserts

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.