INSERT statement

Use the INSERT statement to insert one or more new rows into a table or view, or to insert one or more elements into an SQL or collection variable.

Syntax

>>-INSERT------------------------------------------------------->

                                                                    (1)                         
>--+-INTO--+-+-synonym-+--+------------------+--+-| VALUES Clause |------------------+-+----+-><
   |       | +-view----+  |    .-,------.    |  |                            (2)     | |    |   
   |       | '-table---'  |    V        |    |  +-| EXECUTE Routine Clause |---------+ |    |   
   |       |              '-(----column-+--)-'  |                                (3) | |    |   
   |       |                                    |-| Subset of SELECT Statement |-----+ |    | 
   |       |                                    |                                    | |    |   
   |       |                                    '-(-| Subset of SELECT Statement |-)-' |    |  
   |       '-external--+------------------+--+------------------------------------+----'    |   
   |                   |    .-,------.    |  |                                (3) |         |   
   |                   |    V        |    |  '-| Subset of SELECT Statement |-----'         |   
   |                   '-(----column-+--)-'                                                 |   
   |                                                             (6)                    (7) |   
   '-+---------------------+--INTO--| Collection-Derived Table |------| Field Options |-----'   
     |             (4) (5) |                                                                    
     '-AT position---------'


Element	Description	Restrictions	Syntax
column	Column to receive new value	See Specifying Columns.	Identifier
external	External table into which to insert data	Must exist	Database Object Name
field	Field of a named or unnamed ROW data type	Must already be defined in the database	CREATE ROW TYPE statement
position	Position at which to insert an element of a LIST data type	Literal integer or an INT or SMALLINT type SPL variable.	Literal Number
synonym, table, view	Table, view, or synonym in which to insert data	Synonym or view and the table to which it points must exist	Database Object Name

Usage

To insert data into a table, you must either own the table or have the Insert privilege for the table (see GRANT statement). To insert data into a view, you must have the required Insert privilege, and the view must meet the requirements explained in Inserting Rows Through a View.

If the table or view has data integrity constraints, the inserted rows must meet the constraint criteria. If they do not, the database server returns an error. If the checking mode is set to IMMEDIATE, all specified constraints are checked at the end of each INSERT statement. If the checking mode is set to DEFERRED, all specified constraints are not checked until the transaction is committed.