Sybase Adaptive Server IQ 12.4.2, Administration And Performance Manual

Page: 222 / 536

Converting data on insertion
202
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.
Converting data on insertion
The data you enter into your Adaptive Server IQ database will likely come
from diverse sources. Not all of your data will match the Adaptive Server IQ
data types exactly. Some of it will need to be converted. Data is converted in
two ways: explicitly and implicitly. For example, to insert
CHAR
data into an
INT
column you must convert it explicitly.
Implicit conversions can occur:
• When you insert data selected from another column in the same database