INSERT
Function
The INSERT statement inserts a new row into the target table using values derived from the row amendment expression.
Syntax
INSERT INTO table_specification [(column_specification [,])] [QUERY expression | VALUES (expression[,])]
where
table_specification | A qualified or unqualified identifier which refers to the table to be amended. The table must be defined at this statement's compilation time. If the table specification is a view reference this view must be updatable. See
Table Specification for more details. |
expression | Defines the value which is to be assigned to the specified column. |
query_expression | Defines a resultant table which will be used as a source of values for assignment to the specified columns. See
Query Expression. |
Description
An INSERT statement inserts a number of new rows into the target table as specified by the row amendment expression.
If the target table in an INSERT statement is a subtable, the values assigned to the foreign key columns must be equal to the values contained in the associated referenced key columns of the master table. This is compatible with the concepts of referential integrity. CONNX uses these key values to identify the record, and in case of a level 2 target table the periodic group within the record, into which the new candidate row is to be inserted. The insertion of a row should not result in an insertion of a new record but rather in the insertion of a new occurrence. If the specified foreign key values do not correspond to any referenced key values, then a referential constraint violation is issued.
If the row amendment expression uses a query specification as its means of defining the input, then multiple rows may be inserted, otherwise the insertion of a single row will result.
If the query specification results in no rows, then no rows are inserted and the field sqlcode in the SQLCA is set to +100.
Limitations
If the target table is a view, this view must be updatable as described in the section DECLARE CURSOR.
It is only possible to insert rows into subtables, if the corresponding referenced key columns in the master table exist and are specified with the same value.
The special register SEQNO must not be specified as a target column. However, a value can be specified for a level 0 named SEQNO column. This value will then be the Adabas ISN. The values for level 1 and level 2 named SEQNO columns are occurrence numbers which are generated automatically and can not be specified in an insert operation. One exception is that the level 1 named SEQNO column may be assigned a value when the target table is a level 2 table and the value assigned to the level 1 named SEQNO column already exists.
An empty string or zero value can not be inserted into columns which have been defined with SUPPRESSION (i.e. the Adabas NU option) and with the NOT NULL option, as these two values actually represent the NULL value.
An empty string or zero value can not be inserted into a column that maps to an Adabas multiple-value field defined with SUPPRESSION, as these values are not representable under these conditions.
Examples
The following example inserts a new row into the table cruise:
INSERT INTO cruise (cruise_id , start_date, start_time, end_date, end_time, start_harbor ,
destination_harbor, cruise_price, bunk_number, bunks_free, id_yacht,
id_skipper, id_predecessor , id_successor )
values ( 5037, 1234,19920925,12,19921206,14,'ACAPULCO',
'LIVERPOOL', 2050, 7, 10 146, 244, 5037, 5039)