Sybase 12.4.2 manual Partial-width insertions, 197

CHAPTER 5 Moving Data In and Out of Databases

An easy way to enforce the integrity is to create and run stored procedures that roll back any transaction that violates a constraint. You can use an EXISTS clause to specify violations.

Partial-width insertions

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. Adaptive Server IQ also lets you insert individual columns into an existing row, if you specify its rowid.

Apartial-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 Adaptive Server 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.

197