By default, new rows are inserted wherever there is space in the indexes, and each LOAD TABLE or INSERT statement starts a new row. This approach works as long as the data you are inserting is a new row. Sybase IQ also lets you insert individual columns into an existing row, if you specify its rowid.
A partial-width insertion, also called a vertical insertion, is an insertion into a subset of columns in a table. You can use two or more partial-width insertions to insert data into all of the columns of the table.
Partial-width insertions let you:
Insert data into just a few columns at a time. This approach can be helpful if you have memory limitations.
For example, you can insert data into a few columns at a time, using separate LOAD TABLE or INSERT statements for each group of indexes and using the START ROW ID option to keep the ROW IDs consistent and the memory requirement lower. You may want to do this if you are inserting into a very wide table and do not have enough free memory to populate all the indexes at one time.
Use different data sources, such as multiple flat files, to insert into different groups of columns in a table.
Add a new column and corresponding index to a table after you have already inserted data into the columns for that table. For more information, see the ALTER INDEX command.
WARNING! This is an advanced operation. If you do not perform all the steps correctly in a partial-width insert, you may insert data incorrectly. Never use this type of insert unless you are an experienced Sybase IQ user and are very familiar with your data. Full-width inserts, which insert into all the column indexes on a table at the same time, ensure row-level integrity and are less error-prone.
Use START ROW ID to specify at which row you want to start the insert. This allows you to insert into some of the columns in a row with one partial-width INSERT or LOAD TABLE statement, and insert into the other columns in the same row with additional INSERT or LOAD TABLE statements.
Partial width inserts are not recommended on partitioned tables, as the START ROW ID clause of the LOAD TABLE and INSERT commands is not supported on a partitioned table.
If you try to insert into a column that already contains data, you get an error.
You must be sure to control the row at which each insertion starts. If you do not use START ROW ID, your insertion begins with the next row, and NULLs are inserted in the remaining columns of the current row, as shown in Figure 7-1. (The two shading patterns represent data inserted into columns in two separate insert operations.)
Figure 7-1: Using START ROW ID with partial-width insertions
Do not try to perform a partial-width insertion using
the INSERT VALUES command format. Because you
cannot specify START ROW ID using this format,
the problem shown in the figure results.
