You use INSERT to insert rows into an existing table.
If you want to insert rows into a table, you must either own the table or have the INSERT privilege for the table. Furthermore, the transaction mode of the current transaction must be READ WRITE.
The special literals (see "Special literals") which occur in the INSERT statement (and in preset values) and the time functions CURRENT_DATE, CURRENT_TIME and CURRENT_TIMESTAMP are evaluated once, and the calculated values apply for all inserts.
If integrity constraints have been defined for the table or the columns involved, these are checked after the rows have been inserted. If an integrity constraint has been violated, the insertion is canceled and an appropriate SQLSTATE set.
INSERT INTO table
{ [ column_list ] [COUNT INTO column ] value-declaration | DEFAULT VALUES }
[RETURN INTO parameter-declaration ]
value-declaration ::= { query_expression | VALUES { row_2 , row_2 ,... | row_1 } }
parameter-declaration ::=
{
: host_variable [[INDICATOR] : indicator_variable ] |
routine_parameter |
local_variable
}
column_list ::= (
{
column |
column[posno] |
column[min .. max] |
column ( posno ) |
column ( min .. max )
},...
)
row_2 ::= { ( insert_expression_2 ,...) | insert_expression_2 }
row_1 ::= { ( insert_expression_1 ,...) | insert_expression_1 }
insert_expression_2 ::= { expression | NULL }
insert_expression_1 ::= { expression | NULL | DEFAULT | * | <{ value | NULL},...> }
table
Name of the table into which the rows are to be inserted. The table can be a base table or an updatable view.
column_list
Lists the columns, and the occurrence ranges of multiple columns, for which the INSERT statement specifies the values in the rows to be inserted, and stipulates the order for this. The values of the remaining columns in the rows to be inserted are not specified in the INSERT statement; they are DEFAULT or NULL values or values defined by SESAM/SQL.
No column_list specified:
The INSERT statement specifies the values in the rows to be inserted for each column of table (except for the column specified by COUNT INTO), in the order specified with CREATE TABLE and ALTER TABLE or with CREATE VIEW.
column
Atomic column whose values in the rows to be inserted are specified in the INSERT statement.
column must be a column of the specified table. The order in which you specify the columns does not have to be the same as the order of the columns in the table. You can specify an atomic column only once in the column list.
column(pos_no) /column[pos_no]
Element of a multiple column whose values in the rows to be inserted are specified in the INSERT statement. The multiple column must be part of the table.
If several elements of a multiple column are specified, the range of indexes specified must be contiguous. None of the elements of the multiple column may occur more than once.
pos_no is an unsigned integer >= 1.
column(min..max) / column[min..max]
Elements in a multiple column whose values are indicated in the rows to be inserted in the INSERT statement. The multiple column must be part of the table.
If several elements of a multiple column are specified, the range of indexes specified must be contiguous. None of the elements of the multiple column may occur more than once.
min and max are unsigned integers >= 1; max must be >= min.
COUNT INTO column
Atomic column whose values in the rows to be inserted are determined by SESAM/SQL and must not be specified in the INSERT statement (counting column). column may not occur in column_list.
The column must be of integer or fixed-point number type (SMALLINT, INT, DECIMAL, NUMERIC) and must belong to the primary key. The column may not be contained either in a referential constraint or a check constraint of the table table.
SESAM/SQL determines the values of the respective column in all rows to be inserted in such a way that the primary key values are unique within the table.
query_expression
query expression is a query expression whose derived table specifies the required column values of the rows to be inserted. One row is inserted into the table table for each row of the result table. If query expression returns an empty table, no rows are inserted and an appropriate SQLSTATE is set, which can be handled with WHENEVER NOT FOUND.
VALUES clause
The required column values are specified separately for each row which is to be inserted using line_2 or line_1. The table consisting of all these rows or of this one row plays the same role as the result table of the query_expression.
row_2
The number of rows inserted is the number of times row_2 is specified.
All occurrences of row_2 must have the same number of columns. The data type of each column of the result table follows from the rules described under "Data type of the derived column for UNION". If a column in the result table only contains NULL, its data type will be that of the corresponding column of table.
insert_expression_2
expression
The expression of insert_expression_2 must be atomic.
NULL
The corresponding column in the rows to be inserted must be atomic. It is set to the NULL value.
row_1
One row is inserted. The result table with the required values for this row to be inserted consists of row_1.
insert_expression_1
expression
The expression of insert_expression_1 must be either atomic or a host variable which is a vector with more than one element. If such a host variable or an aggregate is specified, the number of vector or aggregate elements must agree with the number of elements of the respective column in the row to be inserted.
NULL
The respective column in the row to be inserted must be atomic. It is set to the NULL value.
DEFAULT
The respective column in the row to be inserted must be atomic. It is set to the default value. The default value is specified in the definition of the column. If there is no default value defined, the column is set to the NULL value.
*
The corresponding column in the row to be inserted must be atomic, must be of integer or fixed-point number type (SMALLINT, INT, DECIMAL, NUMERIC) and must belong to a primary key. The column may not be contained either in a referential constraint or a check constraint of the table table.
* may occur only once in the VALUES clause and must not occur together with COUNT INTO.
The value of the corresponding column in the row to be inserted is determined by SESAM/SQL in such a way that the primary key values within the table are unique.
<{value, NULL},...>
Aggregate to be assigned to a multiple column. The number of values must be the same as the number of column elements.
Query_expression, insert_expression_2 and insert_expression_1 must not reference a table referring to the underlying base table into which the new rows are inserted. In particular, you may not reference table.
The number of columns of query_expression, row_2 and row_1 must equal the number of column values to be specified for each inserted row, as specified with column list and COUNT INTO. The i-th column of the result table contains the values for the i-th column in column list (if column list is specified), or for the i-th column of table (where a column specified with COUNT INTO is skipped).
The assignment rules specified in section "Entering values in table columns" apply to these assignments.
Any remaining columns of the inserted rows are set as follows:
The column specified by COUNT INTO is set to a value defined by SESAM/SQL.
Columns with a default value are set to the default value (DEFAULT).
Columns without a default value are set to the NULL value.
If table is a view, the rows will be inserted into the underlying base table; columns of the base table not contained in the view will be set in the same way.
DEFAULT VALUES
Inserts one row into the table; the row consists entirely of the column-specific default values.
Columns with an explicitly defined default value are assigned this default value. Columns without explicitly defined default are assigned the NULL value.
RETURN INTO
The value determined by SESAM/SQL for the column specified with COUNT INTO or for * as insert_expression_1 is stored in an output destination. If several rows are inserted, the last value determined by SESAM/SQL will be stored.
You can only use the RETURN INTO clause, if either COUNT INTO is specified, or a * is used as insert_expression_1.
:host_variable, routine_parameter, local_variable
Name of a host variable (if the statement is not part of a routine) or name of a procedure parameter of the type INOUT or OUT or of a local variable (if the statement is part of a routine). The value of the count column is assigned to the specified output destination.
The output destination must be of numeric data type.
indicator_variable
Name of the indicator variable for the preceding host variable.
Inserting values for multiple columns
In the case of a multiple column, you can insert values for individual column elements or for ranges of elements.
An element of a multiple column is identified by its position number in the multiple column.
A range of elements in a multiple column is identified by the position numbers of the first and last element in the range.
INSERT and integrity constraints
By specifying integrity constraints when you define the base table, you can restrict the range of values for the corresponding columns. The value specified in the INSERT statement must satisfy the defined integrity constraint.
INSERT and transaction management
INSERT initiates an SQL transaction outside routines if no transaction is open. If you define an isolation level, you can control how the INSERT statement can be affected by concurrent transactions (see section "SET TRANSACTION - Define transaction attributes").
If an error occurs during the INSERT statement, any rows that have already been inserted are removed.
Examples
Each of the following two statements inserts three rows into the ORDERS table. In the second INSERT statement, the value for the primary key is determined by SESAM/SQL. The last value assigned is stored in the host variable ORDNUMRET.
INSERT INTO orders (order_num,cust_num,contact_num,order_text,actual,orderstat)
VALUES (345, 101, 20, 'Network:installation', DATE'<date>', 1),
(346, 101, 20, 'Network:installation', DATE'<date>', 1),
(347, 101, 20, 'Network:installation', DATE'<date>', 1),
INSERT INTO orders (cust_num,contact_num,order_text,actual,orderstat)
COUNT INTO order_num
VALUES (:CUST_NUM,:CONTACT_NUM,:ORDERTEXT1,:ACTUAL1,:ORDERSTAT),
(:CUST_NUM,:CONTACT_NUM,:ORDERTEXT1,:ACTUAL1,:ORDERSTAT),
(:CUST_NUM,:CONTACT_NUM,:ORDERTEXT1,:ACTUAL1,:ORDERSTAT),
RETURN INTO :ORDNUMRET
In a table with the name WOMEN, the columns FNAME and LNAME are defined in the same way as in the table CONTACTS. The following INSERT statement adds all female contacts to the WOMEN table:
INSERT INTO women (fname, lname)
SELECT fname, lname
FROM contacts
WHERE title IN ('Frau','Fraeulein','Mrs.','Ms.')
See also
DELETE, MERGE, UPDATE