Common Set Syntax:
INSERT
INTO
table-name
|
Extended Set Syntax:
INSERT
INTO
table-name
|
( |
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
The SQL INSERT
statement is used to add one or more new
rows to a table.
Syntax Element | Description |
---|---|
INTO
table-name
|
INTO Clause:
In the See further information on
|
column-list
|
Column List:
Syntax: column-name... In the If a If the |
VALUES-clause |
Values Clause:
With the See VALUES Clause below. |
OVERRIDING USER
VALUE
|
OVERRIDING USER VALUE Clause:
This clause belongs to the SQL Extended Set. This clause is not currently supported. When used, it will cause a compiler error. |
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.
VALUES (
|
) |
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 |
[FOR-n-ROWS-clause] |
Extended Set Syntax:
VALUES |
[FOR-n-ROWS-clause] | |||||||||||||
[WITH_CTE
common-table-expression,...]
select-expression |
WITH |
[QUERYNO
integer]
|
Syntax Description:
Syntax Element | Description | |
---|---|---|
VIEW
view-name |
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 If no The values to be specified in the
See the section Basic Syntactical Items for
information on If the value Example - INSERT Single Row: ... INSERT INTO SQL-PERSONNEL (NAME,AGE) VALUES ('ADKINSON',35) ... |
|
FOR-n-ROWS-clause |
FOR n Rows
Clause:
Optional clause, see FOR-n-ROWS-Clause below. |
|
WITH_CTE
common-table-expression |
WITH_CTE Clause:
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 WITH CTE common-table-expression,... in the section SELECT - Cursor-Oriented. |
|
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. It is only valid against DB2 databases. When used against other databases, it will cause runtime errors. |
|
CS |
Cursor Stability | |
RR |
Repeatable Read | |
RS |
Read Stability | |
QUERYNO_integer
|
QUERYNO Clause:
This clause belongs to the SQL Extended Set. This clause is not currently supported and will be ignored. |
FOR |
[:]_host-variable |
ROWS |
[atomic-clause] |
This clause is composed of the following subclauses:
FOR |
[:]_host-variable |
ROWS |
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.
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:
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