Sybase 12.4.2 manual Index, 201

CHAPTER 5 Moving Data In and Out of Databases

29	3	28	1376	3
200	4	13	119	4
59	5	9	4	5

(5 rows affected)

Using the FILLER Option

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 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)

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

201