Filter
Top Products
CHAPTER 5 Moving Data In and Out of Databases
197
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.
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 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.