Common Set Syntax:
INSERT
INTO
table-name |
(* )
[VALUES-clause]
|
||
[(column-list)] VALUE-LIST |
Extended Set Syntax:
INSERT
INTO
table-name |
(* )
[OVERRIDING USER
VALUE ] [VALUES-clause]
|
||
[(column-list)]
[OVERRIDING USER VALUE ]
VALUE-LIST |
This documentcovers the following topics:
For an explanation of the symbols used in the syntax diagram, see Syntax Symbols.
Belongs to Function Group: Database Access and Update
See also the following sections in the Database Management System Interfaces documentation:
NDB - INSERT in the Natural for DB2 part.
INSERT in the Natural for SQL/DS part.
The SQL INSERT
statement is used to add one or more new
rows to a table.
INTO table-name |
INTO Clause:
In the See further information on table-name. |
---|---|
column-list |
Column List:
Syntax: column-name... In the column-list, one or more column-names can be specified, which are to be supplied with values in the row currently inserted. If a column-list is specified, the sequence of the columns must match with the sequence of the values either specified in the insert-item-list or contained in the specified view (see below). If the column-list is omitted, the values in the insert-item-list or in the specified view are inserted according to an implicit list of all the columns in the order they exist in the table. |
VALUES-clause |
Values Clause:
With the |
insert-item-list |
INSERT Single Row:
In the insert-item-list, you can specify one or more values to be assigned to the columns specified in the column-list. The sequence of the specified values must match the sequence of the columns. If no column-list is specified, the values in the insert-item-list are inserted according to an implicit list of all the columns in the order they exist in the table. The values to be specified in the
insert-item-list can be
constants, parameters,
special-registers or See the section Basic Syntactical Items for information on view-name, constant and parameter. See also the information on special-register. If the value Example - INSERT Single Row: ... INSERT INTO SQL-PERSONNEL (NAME,AGE) VALUES ('ADKINSON',35) ... |
OVERRIDING USER VALUE |
OVERRIDING USER VALUE Clause:
This clause belongs to the SQL Extended Set. This clause causes the value specified in the
|
With the VALUES
clause, you insert a single row
into the table. Depending on whether an asterisk (*) or a
column-list has been specified, the
VALUES
clause can take one of the following forms:
VALUES (VIEW view-name)
|
If asterisk notation is specified, a view must be specified in
the VALUES
clause. With the field values of this view, a new row
is inserted into the specified table using the field names of the view as
column names of the row.
[(column-list)] [OVERRIDING USER VALUE ]
VALUE-LIST |
If a column-list is specified and a view is
referenced in the VALUES
Clause, the number of items specified in
the column list must correspond to the number of fields defined in the view
within the VALUE-LIST.
If no column-list is specified, the fields defined in the view are inserted according to an implicit list of all the columns in the order they exist in the specified table.
Common Set Syntax:
VALUES |
(VIEW view-name)
|
[FOR-n-ROWS-clause] | ||||||||||||
(insert-item-list) |
Extended Set Syntax:
VALUES |
(VIEW view-name)
|
[FOR-n-ROWS-clause] | ||||||||||||
(insert-item-list) | ||||||||||||||
[WITH_CTE
common-table-expression,...]
select-expression |
WITH |
RR |
[QUERYNO
integer]
|
|||||||||||
RS |
||||||||||||||
CS |
Syntax Description:
VIEW view-name | With the field values of this view, a new row is inserted into the specified table using the field names of the view as column names of the row. |
---|---|
insert-item-list |
INSERT Single Row:
In the insert-item-list, you can specify one or more values to be assigned to the columns specified in the column-list. The sequence of the specified values must match the sequence of the columns. If no column-list is specified, the values in the insert-item-list are inserted according to an implicit list of all the columns in the order they exist in the table. The values to be specified in the
insert-item-list can be constants, parameters,
special-registers or See the section Basic Syntactical Items for information on view-name, constant and parameter. See also the information on special-register. If the value Example - INSERT Single Row: ... INSERT INTO SQL-PERSONNEL (NAME,AGE) VALUES ('ADKINSON',35) ... |
FOR-n-ROWS-clause | Optional clause, see below. |
WITH_CTE common-table-expression |
This clause belongs to the SQL Extended Set. This optional clause permits defining a result table which can be
referenced in any For more information, see SELECT - Cursor-Oriented, WITH CTE common-table-expression,.... |
select-expression |
INSERT Multiple Rows:
This clause belongs to the SQL Extended Set. With a select-expression, you insert
multiple rows into a table. The
select-expression is evaluated and each row of the
result table is treated as if the values in this row were specified as values
in a VALUES
Clause of a single-row For further information, see Select Expressions. Example - INSERT Multiple Rows: ... INSERT INTO SQL-RETIREE (NAME,AGE,SEX) SELECT LASTNAME, AGE, SEX FROM SQL-EMPLOYEES WHERE AGE > 60 ... Note: |
WITH RR/RS/CS |
WITH Isolation Level Clause:
This clause belongs to the SQL Extended Set. This clause allows the explicit specification of the isolation level used when locating the rows to be inserted. |
QUERYNO_integer |
QUERYNO Clause:
This clause belongs to the SQL Extended Set. This clause explicitly specifies the number to be used in
|
FOR |
[:]_host-variable | ROWS |
ATOMIC |
||||||
integer | NOT ATOMIC
CONTINUE ON SQLEXCEPTION |
This clause is composed of the following subclauses:
FOR |
[:]_host-variable | ROWS |
||
integer |
The specification of this clause is optional. It should only be specified, if
compiler option DB2ARRY
is specifed
and multiple rows are to be inserted from arrays specified in the insert-item-list of the VALUES Clause.
If specified, [:]_hostvariable/integer determines the number of rows to be inserted into the DB2 table from the arrays specified in the insert-item-list of the VALUES Clause starting with the first occurrence.
The purpose of this clause is to improve the performance of programs inserting rows from Natural arrays in a loop. By using this clause, the rows contained in the arrays can be inserted by one SQL statement.
See example below.
See also the Natural for DB2 part in the Database Management System Interfaces documentation.
ATOMIC |
||||
NOT ATOMIC
CONTINUE ON SQLEXCEPTION |
This clause specifies whether the insertion of multiple rows should be treated by DB2 as an atomic operation or not.
It should only be specified, if
compiler option DB2ARRY is specifed
and multiple rows are to be inserted from arrays specified in the insert-item-list of the VALUES Clause.
Syntax Description:
ATOMIC | Specifies that in case of any error no row is inserted into the target table. This is the default value. |
---|---|
NOT ATOMIC CONTINUE ON SQLEXCEPTION | Specifies that in case of errors all rows for which no error occurred are inserted while those rows for which errors occurred are discarded by DB2. |
See the DB2 SQL REFERENCE for sqlcodes returned in such cases.
DEFINE DATA LOCAL 01 NAME (A20/1:10) INIT <'ZILLER1','ZILLER2','ZILLER3','ZILLER4' ,'ZILLER5','ZILLER6','ZILLER7','ZILLER8' ,'ZILLER9','ZILLERA'> 01 ADDRESS (A100/1:10) INIT <'ANGEL STREET 1','ANGEL STREET 2' ,'ANGEL STREET 3','ANGEL STREET 4' ,'ANGEL STREET 5','ANGEL STREET 6' ,'ANGEL STREET 7','ANGEL STREET 8' ,'ANGEL STREET 9','ANGEL STREET 10'> 01 DATENATD (D/1:10) INIT <D'1954-03-27',D'1954-03-27',D'1954-03-27' ,D'1954-03-27',D'1954-03-27',D'1954-03-27' ,D'1954-03-27',D'1954-03-27',D'1954-03-27' ,D'1954-03-27'> 01 SALARY (P4.2/1:10) INIT <1000,2000,3000,4000,5000 ,6000,7000,8000,9000,9999> 01 L§ADDRESS (I2/1:10) INIT <14,14,14,14,14,14,14,14,14,15> 01 N§ADDRESS (I2/1:10) INIT <00,00,00,00,00,00,00,00,00,00> 01 ROWS (I4) 01 INDEX (I4) 01 V1 VIEW OF NAT-DEMO_ID 02 NAME 02 ADDRESS (EM=X(20)) 02 DATEOFBIRTH 02 SALARY 01 ROWCOUNT (I4) END-DEFINE OPTIONS DB2ARRY=ON /* <-- ENABLE DB2 ARRAY ROWCOUNT := 10 INSERT INTO NAT-DEMO_ID (NAME,ADDRESS,DATEOFBIRTH,SALARY) VALUES (:NAME(*), /* <-- ARRAY :ADDRESS(*) /* <-- ARRAY INDICATOR :N§ADDRESS(*) /* <-- ARRAY LINDICATOR :L§ADDRESS(*), /* <-- ARRAY DB2 VCHAR :DATENATD(1:10), /* <-- ARRAY NATURAL DATES :SALARY(01:10) /* <-- ARRAY NATURAL PACKED ) FOR :ROWCOUNT ROWS SELECT * INTO VIEW V1 FROM NAT-DEMO_ID WHERE NAME > 'Z' DISPLAY V1 /* <-- VERIFY INSERT END-SELECT END