Sybase 12.4.2 Manuale Utente

CHAPTER 5    Moving Data In and Out of Databases

•

 

For 

DATE

, 

TIME

, and 

TIMESTAMP

 or 

DATETIME 

columns, you must use a 

specific format. See “Converting data on insertion” for information on 
data type conversions. See the Adaptive Server IQ Reference Manual for 
a complete description of Adaptive Server IQ data types.

The 

TIMESTAMP

 and 

DATETIME 

data types are identical.

Allowing NULL values

When you specify values for only some of the columns in a row, NULL is 
inserted for columns with no value specified, if the column allows NULL. If 
you specify a NULL value, the destination column must allow NULLs, or the 
INSERT is rejected and an error message is produced in the message log. 
Adaptive Server IQ columns allow NULLs by default, but you can alter this by 
specifying 

NOT NULL

 on the column definition in the 

CREATE TABLE

 

statement or in other ways, such as using a primary key, for example.

Example

The following example adds 1995-06-09 into the 

l_shipdate

 column and 123 

into the 

l_orderkey

 column in the 

lineitem

 table.

INSERT INTO lineitem

    (l_shipdate, l_orderkey)

VALUES(’1995-06-09’, 123)

If you are inserting more than a small number of data rows, it is more efficient 
to insert selected rows directly from a database, as described in the next 
section, or to load data from a flat file with the 

LOAD TABLE 

statement, than to 

insert values row by row. Consider using a select statement with a few unions 
instead of inserting values for a few rows, because this requires only a single 
trip to the server.

To insert data from other tables in the current database, or from a database that 
is defined as a Specialty Data Store to Adaptive Server IQ, use this syntax:

INSERT [ INTO ] 
[ 

owner.]table_name

[ (

column-name,...) ]

[ 

insert-load-options ]...

select-statement

insert-load-options:
LIMIT 

number-of-rows

number-of-rows