Entering content frame

INSERT Statement (insert_statement) Locate the document in its SAP Library structure

The INSERT statement (insert_statement) creates new rows in a table.

Syntax

<insert_statement> ::=
  INSERT [INTO] <table_name> [(<column_name>,...)] VALUES (<insert_expression>,...) [<duplicates_clause>]
| INSERT [INTO] <table_name> [(<column_name>,...)] <query_expression> [<duplicates_clause>]
| INSERT [INTO] <table_name> SET <set_insert_clause>,... [<duplicates_clause>]

<insert_expression> ::= <extended_expression> | <subquery>
<extended_expression> ::= <expression> | DEFAULT | STAMP

<set_insert_clause> ::= <column_name> = <extended_value_spec>

Explanation

The table name must identify an existing base table, view table, or a synonym.

The user must have the INSERT privilege for the table identified by the table name. If the table name identifies a view table, even the owner of the view table may not have the INSERT privilege because the view table cannot be changed.

<column_name>, <set_insert_clause>

If column names column_name or a SET INSERT clause set_insert_clause are specified, all of the specified column names must be columns in the table. If the table was defined without a key (that is, if the SYSKEY column is created internally by the database system), the SYSKEY column must not occur in the sequence of column names or in a SET INSERT clause. A column must not occur more than once in a sequence of column names or in more than one SET INSERT clause.

A specified column (identified by column_name or the column name in the set_insert_clause) is a target column. Target columns can be specified in any order.

·        If a column name and SET INSERT clause are not specified, the result is the same as if a sequence of columns were specified that contains all of the columns in the table in the order specified in the CREATE TABLE statement or CREATE VIEW statement. In this case, every table column defined by the user is a target column.

·        The number of insert_expression expressions must be equal to the number of target columns. The ith expression is assigned to the ith column name.

·        Expressions extended_expression and subqueries subquery can be specified simultaneously.

·        You can specify one or more subqueries.

·        The specified subqueries must return only one single result line.

·        The number of selected columns specified in the QUERY expression query_expression must be identical to the number of target columns.

·        All mandatory columns of the table identified by table name must be target columns.

·        If the table name identifies a view table, rows are inserted the base table(s) on which the view table is based. In this case, the target columns of the specified table name correspond to columns of the base tables on which the view table is based. In the following, the term target column always refers to the corresponding column in the base table.

<extended_expression>

An extended expression extended_expression can be specified by means of an expression or one of the keywords DEFAULT or STAMP.

·        Expression expression 
An expression in an INSERT statement must not contain a column specification.
The value specified by a parameter specification in an expression is the value of the parameter identified by the specification. If an indicator parameter is specified with a negative value, the value defined by the parameter specification is a NULL value.

·        Keyword DEFAULT
DEFAULT denotes the value used as the DEFAULT for the column.

·        Keyword STAMP
The database system is able to generate unique values. Such values are serial numbers beginning at X'000000000001' and are assigned in ascending order. It cannot be ensured that a sequence of values is uninterrupted. The keyword STAMP supplies the next value generated by the database system.
It can be used in the INSERT statement or the UPDATE statement, though only on columns of data type CHAR(n) BYTE where n>=8.
If the user wants to find out the generated value before it is applied to the column, the SQL statement NEXT STAMP statement must be used.

Further Information

·        Data Type of the Target Column and Inserted Value

·        Join View Table, QUERY Expression, CONSTRAINT Definition, Trigger in INSERT Statement

 

Leaving content frame