When you use the INSERT statement to insert data directly from a database rather than from a flat file, you cannot use the load conversion options. If the data requires explicit conversion, you must use one of the conversion functions, CAST or CONVERT, in the SELECT statement or VALUES list where you specify the data to be inserted. If the data is converted implicitly, Sybase IQ handles the conversion automatically.
An implicit or explicit conversion is required whenever data types in a SELECT statement need to match, but do not. This occurs when you do an INSERT SELECT from one data type to another, but it also occurs whenever you compare or compute values for differing data types.
The following tables show:
Which conversions Sybase IQ does implicitly (I)
Which conversions you must do explicitly (E)
Which conversions are unsupported (U)
These conversions apply to data within a Sybase IQ database, or coming from an SQL Anywhere database, or any other database connected as a Specialty Data Store.
The first table shows implicit (I), explicit (E), and unsupported (U) conversions when there is no WHERE clause in the SELECT statement, or when the WHERE clause is based on a comparison operation (=, > or <).
To: |
|||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
From: |
ti |
si |
in |
ui |
bi |
ub |
nu |
rl |
dl |
bt |
dt |
tm |
ts |
ch |
vc |
bn |
vb |
tinyint |
I |
I |
I |
I |
I |
I |
I |
I |
I |
I |
E |
E |
E |
E |
E |
I |
I |
smallint |
I |
I |
I |
I |
I |
I |
I |
I |
I |
I |
E |
E |
E |
E |
E |
I |
I |
int |
I |
I |
I |
I |
I |
I |
I |
I |
I |
I |
E |
E |
E |
E |
E |
I |
I |
unsigned int |
I |
I |
I |
I |
I |
I |
I |
I |
I |
I |
E |
E |
E |
E |
E |
I |
I |
bigint |
I |
I |
I |
I |
I |
I |
I |
I |
I |
I |
E |
E |
E |
E |
E |
I |
I |
unsigned bigint |
I |
I |
I |
I |
I |
I |
I |
I |
I |
I |
E |
E |
E |
E |
E |
I |
I |
numeric |
I |
I |
I |
I |
I |
I |
I |
I |
I |
I |
E |
E |
E |
E |
E |
U |
U |
real |
I |
I |
I |
I |
I |
I |
I |
I |
I |
I |
E |
E |
E |
E |
E |
U |
U |
double |
I |
I |
I |
I |
I |
I |
I |
I |
I |
I |
E |
E |
E |
E |
E |
U |
U |
bit |
I |
I |
I |
I |
I |
I |
I |
I |
I |
I |
U |
U |
U |
I |
I |
I |
I |
date |
E |
E |
E |
E |
E |
E |
E |
E |
E |
U |
I |
U |
I |
E |
E |
U |
U |
time |
E |
E |
E |
E |
E |
E |
E |
E |
E |
U |
U |
I |
E |
E |
E |
U |
U |
time-stamp |
E |
E |
E |
E |
E |
E |
E |
E |
E |
U |
E |
I |
I |
E |
E |
U |
U |
char |
E |
E |
E |
E |
E |
E |
E |
E |
E |
I |
E |
E |
E |
I |
I |
I |
I |
varchar |
E |
E |
E |
E |
E |
E |
E |
E |
E |
I |
E |
E |
E |
I |
I |
I |
I |
binary |
I |
I |
I |
I |
I |
I |
U |
U |
U |
U |
U |
U |
U |
I |
I |
I |
I |
varbinary |
I |
I |
I |
I |
I |
I |
U |
U |
U |
U |
U |
U |
U |
I |
I |
I |
I |
The following list contains the descriptions of the codes used in Table 7-2, Table 7-3, and Table 7-4:
Code |
Data type |
Code |
Data type |
Code |
Data type |
---|---|---|---|---|---|
ti |
tinyint |
nu |
numeric |
ts |
timestamp |
si |
smallint |
rl |
real |
ch |
char |
in |
int |
dl |
double |
vc |
varchar |
ui |
unsigned int |
bt |
bit |
bn |
binary |
bi |
bigint |
dt |
date |
vb |
varbinary |
ub |
unsigned bigint |
tm |
time |
The second table shows implicit (I), explicit (E), and unsupported (U) conversions when the WHERE clause in a SELECT statement is based on an arithmetic operation (+, –, etc.).
To: |
|||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
From: |
ti |
si |
in |
ui |
bi |
ub |
nu |
rl |
dl |
bt |
dt |
tm |
ts |
ch |
vc |
bn |
vb |
tinyint |
I |
I |
I |
I |
I |
I |
I |
I |
I |
I |
U |
U |
U |
E |
E |
I |
I |
smallint |
I |
I |
I |
I |
I |
I |
I |
I |
I |
I |
U |
U |
U |
E |
E |
I |
I |
int |
I |
I |
I |
I |
I |
I |
I |
I |
I |
I |
U |
U |
U |
E |
E |
I |
I |
unsigned int |
I |
I |
I |
I |
I |
I |
I |
I |
I |
I |
U |
U |
U |
E |
E |
I |
I |
bigint |
I |
I |
I |
I |
I |
I |
I |
I |
I |
I |
U |
U |
U |
E |
E |
I |
I |
unsigned bigint |
I |
I |
I |
I |
I |
I |
I |
I |
I |
I |
U |
U |
U |
E |
E |
I |
I |
numeric |
I |
I |
I |
I |
I |
I |
I |
I |
I |
I |
U |
U |
U |
E |
E |
U |
U |
real |
I |
I |
I |
I |
I |
I |
I |
I |
I |
I |
U |
U |
U |
E |
E |
U |
U |
double |
I |
I |
I |
I |
I |
I |
I |
I |
I |
I |
U |
U |
U |
E |
E |
U |
U |
bit |
I |
I |
I |
I |
I |
I |
I |
I |
I |
I |
U |
U |
U |
I |
I |
I |
I |
date |
U |
U |
U |
U |
U |
U |
U |
U |
U |
U |
U |
I |
U |
U |
U |
U |
U |
time |
U |
U |
U |
U |
U |
U |
U |
U |
U |
U |
I |
U |
U |
U |
U |
U |
U |
timestamp |
U |
U |
U |
U |
U |
U |
U |
U |
U |
U |
U |
U |
U |
U |
U |
U |
U |
char |
E |
E |
E |
E |
E |
E |
E |
E |
E |
I |
U |
U |
U |
I |
I |
I |
I |
varchar |
E |
E |
E |
E |
E |
E |
E |
E |
E |
I |
U |
U |
U |
I |
I |
I |
I |
binary |
I |
I |
I |
I |
I |
I |
U |
U |
U |
U |
U |
U |
U |
I |
I |
I |
I |
varbinary |
I |
I |
I |
I |
I |
I |
U |
U |
U |
U |
U |
U |
U |
I |
I |
I |
I |
In arithmetic operations, bit data is implicitly converted to tinyint.
The third table shows implicit (I), explicit (E), and unsupported (U) conversions for the INSERT and UPDATE statements.
To: |
|||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
From: |
ti |
si |
in |
ui |
bi |
ub |
nu |
rl |
dl |
bt |
dt |
tm |
ts |
ch |
vc |
bn |
vb |
tinyint |
I |
I |
I |
I |
I |
I |
I |
I |
I |
I |
E |
E |
E |
E |
E |
I |
I |
smallint |
I |
I |
I |
I |
I |
I |
I |
I |
I |
I |
E |
E |
E |
E |
E |
I |
I |
int |
I |
I |
I |
I |
I |
I |
I |
I |
I |
I |
E |
E |
E |
E |
E |
I |
I |
unsigned int |
I |
I |
I |
I |
I |
I |
I |
I |
I |
I |
E |
E |
E |
E |
E |
I |
I |
bigint |
I |
I |
I |
I |
I |
I |
I |
I |
I |
I |
E |
E |
E |
E |
E |
I |
I |
unsigned bigint |
I |
I |
I |
I |
I |
I |
I |
I |
I |
I |
E |
E |
E |
E |
E |
I |
I |
numeric |
I |
I |
I |
I |
I |
I |
I |
I |
I |
E |
E |
E |
E |
E |
E |
U |
U |
real |
I |
I |
I |
I |
I |
I |
I |
I |
I |
I |
E |
E |
E |
E |
E |
U |
U |
double |
I |
I |
I |
I |
I |
I |
I |
I |
I |
I |
E |
E |
E |
E |
E |
U |
U |
bit |
I |
I |
I |
I |
I |
I |
I |
I |
I |
I |
U |
U |
U |
I |
I |
I |
I |
date |
E |
E |
E |
E |
E |
E |
E |
E |
E |
E |
I |
U |
I |
E |
E |
U |
U |
time |
E |
E |
E |
E |
E |
E |
E |
E |
E |
E |
U |
I |
E |
E |
E |
U |
U |
time-stamp |
E |
E |
E |
E |
E |
E |
E |
E |
E |
E |
E |
I |
I |
E |
E |
U |
U |
char |
I |
I |
I |
I |
I |
I |
I |
I |
I |
I |
E |
E |
E |
I |
I |
I |
I |
varchar |
I |
I |
I |
I |
I |
I |
I |
I |
I |
I |
E |
E |
E |
I |
I |
I |
I |
binary |
I |
I |
I |
I |
I |
I |
U |
U |
U |
I |
U |
U |
U |
I |
I |
I |
I |
varbinary |
I |
I |
I |
I |
I |
I |
U |
U |
U |
I |
U |
U |
U |
I |
I |
I |
I |