Neoview SQL Reference Manual (R2.4 SP2)

Inserting Character Values
Any character string data type is compatible with all other character string data types that have
the same character set. For fixed length, an inserted value shorter than the column length is
padded on the right with blank characters of the appropriate character set (for example, ISO88591
blanks (HEX20). If the value is longer than the column length, string truncation of nonblank
trailing characters returns an error, and the truncated string is not inserted.
For variable length, a shorter inserted value is not padded. As is the case for fixed length, if the
value is longer than the column length, string truncation of nonblank trailing characters returns
an error, and the truncated string is not inserted.
Inserting Numeric Values
Any numeric data type is compatible with all other numeric data types. If you insert a value into
a numeric column that is not large enough, an overflow error occurs. If a value has more digits
to the right of the decimal point than specified by the scale for the column definition, the value
is truncated.
Inserting Interval Values
A value of interval data type is compatible with another value of interval data type only if the
two data types are both year-month or both day-time intervals.
Inserting Date and Time Values
Date, time, and timestamp are the three Neoview SQL datetime data types. A value with a
datetime data type is compatible with another value with a datetime data type only if the values
have the same datetime fields.
Inserting Nulls
In addition to inserting values with specific data types, you might want to insert nulls. To insert
null, use the keyword NULL. NULL only works with the VALUES clause. Use cast (null
as type) for select-list.
Examples of INSERT
• Insert a row into the CUSTOMER table without using a target-col-list:
INSERT INTO sales.customer
VALUES (4777, 'ZYROTECHNIKS', '11211 40TH ST.',
'BURLINGTON', 'MASS.', '01803', 'A2');
--- 1 row(s) inserted.
The column name list is not specified for this INSERT statement. This operation works
because the number of values listed in the VALUES clause is equal to the number of columns
in the CUSTOMER table, and the listed values appear in the same order as the columns
specified in the CREATE TABLE statement for the CUSTOMER table.
By issuing this SELECT statement, this specific order is displayed:
SELECT * FROM sales.customer
WHERE custnum = 4777;
CUSTNUM CUSTNAME STREET ... POSTCODE CREDIT
------- ------------- -------------- -------- ------
4777 ZYROTECHNIKS 11211 4OTH ST. ... 01803 A2
--- 1 row(s) selected.
• Insert a row into the CUSTOMER table using a target-col-list:
INSERT INTO sales.customer
(custnum, custname, street, city, state, postcode)
VALUES (1120, 'EXPERT MAILERS', '5769 N. 25TH PL',
'PHOENIX', 'ARIZONA', '85016');
INSERT Statement 145