INSERT is an executable command that can be issued interactively, embedded in a host language, or dynamically prepared.
Authority requirements depend on whether the object identified in the statement is a table or view. When rows are inserted into a table, the privileges held by the user must include either the INSERT privilege on the table, ownership of the table, DBADM authority on the database containing the table, or overall SYSADM authority for the location. When rows are inserted into a view, the privileges held by the user must include either the INSERT privilege on the view, or overall SYSADM authority.
Users should be aware that a view owner (unlike a table owner) may not have INSERT authority on the view, since the nature of the view itself could preclude its use for INSERT. Alternatively, the view owner might possess INSERT authority, but be unable to grant it to others. Additionally, if a subselect is specified, the user must possess authority to execute the subselect.
If the INSERT command is prepared at a location other than the current location, the total available privileges include all privileges recorded in the system catalog where the INSERT is executed.
If an expression that refers to a function is specified, the privilege set must include any authority that is necessary to execute the function.
If the statement is embedded in an application program, the privilege set is the privileges that are held by the authorization ID of the owner of the plan or package. If the statement is dynamically prepared, the privilege set is determined by the DYNAMICRULES behavior in effect (run, bind, define, or invoke.)
INSERT INTO {table-name | view-name}
[(column-name [,...])]
[OVERRIDING USER VALUE]
{VALUES {{expression | DEFAULT | NULL} | ({expression | DEFAULT | NULL}[,...]) |
[WITH common-table-expression [,...]] fullselect [isolation-clause] [QUERYNO integer] |
multiple-row-insert}
isolation-clause
WITH {RR | RS | CS
multiple-row-insert
VALUES {expression | host-variable-array | NULL | DEFAULT |
({expression | host-variable-array | NULL | DEFAULT} [,...]})
[FOR [host-variable | integer-constant] ROWS]
[ATOMIC | NOT ATOMIC CONTINUE ON SQLEXCEPTION]
| table-name or view-name | Preceded by the keyword INTO, identifies the object (table or view) of the INSERT statement. If a view, the view must be updatable, in which case the underlying table is affected. A catalog table (or a view of a catalog table) or an auxiliary table cannot be specified. |
| column-list | Specifies an optional list of columns into which the data is to be inserted. The column list, if specified, must be enclosed in parentheses, with each column name separated by a comma. If the column list is omitted, all columns in the target table or view are assigned values -- proceeding in order from left to right. |
| value-list | Preceded by the keyword VALUES, specifies one row of values (composed of constants, host-variables, NULLs, or special-registers) to be inserted into the target table or view. Each host variable named must represent a declared variable or structure, properly described in the application program in accordance with the rules for declaring variables and structures in the host language. |
| subselect | Indicates a subselect that retrieves data from another table to insert into the table named in the INSERT command. The same table cannot be the base object of both the INSERT command and the subselect (or any subquery of the subselect). An INSERT command cannot include both a subselect statement and a VALUES clause. |
Tables referenced in all selects (or subselects) contained in a single INSERT command must be from the same location as the table or view into which data is being inserted. That is, you cannot INSERT into the TUTORIAL location and select from the DEBLOC location.
Invalid:
INSERT INTO tutorial.tutorial.practice SELECT * FROM debloc.tutorial.employee WHERE e_no IN SELECT e_no FROM debloc.debbie.employee WHERE st = "MD";
Valid:
INSERT INTO tutorial.tutorial.practice SELECT * FROM tutorial.tutorial.employee WHERE e_no IN SELECT e_no FROM tutorial.debbie.employee WHERE st = "MD";
The INSERT command is used to insert one or more record(s) into an existing table. Data inserted with the INSERT command can be specified by the user in the INSERT command or retrieved from other tables using a subselect statement. The INSERT command takes on one of the following two configurations:
Specifies that the value specified in the VALUES clause or produced by a subselect for a column that is defined as GENERATED ALWAYS is ignored. Instead, a system-generated value is inserted, overriding the user-specified value.
Specify OVERRIDING USER VALUE only if the insert involves a column defined as GENERATED ALWAYS, such as a ROWID column or an identity column.
The number of individual data items in the VALUES clause must equal the number of items in the column list. The first data item in the value list is inserted in the first column in the column list, the second value in the second column, etc. A value list must be included if a subselect is not specified. The value list must be enclosed in parentheses. Values must be separated from each other by commas. Character strings should appear in quotes (single or double). If the value to be inserted in a column is null, the keyword NULL should appear in the appropriate location in the value list.
| expression | Any expression of the type described in "Expressions" in topic 3.19. The expression must not include a column name. If expression is a single host variable, the host variable can identify a structure. Any host variable or structure that is specified must be described in the application program according to the rules for declaring host structures and variables. |
| DEFAULT | The default value assigned to the column. If the column is a ROWID column or an identity column, DB2 will generate a unique value for the column. You can specify DEFAULT only for columns that have an assigned default value, ROWID columns, and identity columns. |
| NULL | The null value. |
Overrides the isolation level of the statement using the WITH {RR | CS} clause, specifying the isolation level with which a searched INSERT statement is executed. The WITH clause specifies an isolation level only for the INSERT statement in which it appears. WITH UR only applies to read only operations, and cannot be used in INSERT statements. The WITH clause overrides the default isolation level for the statement in which it appears.
Specifies the number to be used for this SQL statement in EXPLAIN output and trace records. The number is used for the QUERYNO columns of the plan tables for the rows that contain information about this SQL statement.
If the clause is omitted, the number associated with the SQL statement is the statement number assigned during precompilation. Thus, if the application program is changed and then precompiled, that statement number might change.
Using the QUERYNO clause to assign unique numbers to the SQL statements in a program is helpful for simplifying the use of optimization hints for access path selection, if hints are used.
If the insert value for a column is a number, the column must be configured as a numeric data type column with the capability of representing the integral part of the inserted number value. If the insert value for a column is a string, the column must be configured either as a string column (with a length greater than or equal to the string length) or a datetime column (if the string represents a date, time, or timestamp).
If a table (or the base table of a view) named in an INSERT statement contains unique indexes, then each row of values inserted into the table must comply with the uniqueness constraints imposed by these indexes. If the table has a check constraint, each row of data must comply with the check condition.
When a view named in an INSERT statement includes a WITH CHECK OPTION clause in its definition, each row inserted into the view must comply with the view as specified in its definition. If the named view contains other views in its definition -- and these views happen to contain WITH CHECK OPTION clauses in their definitions, the row data values inserted must also comply with these other views as specified in their definitions. An INSERT statement might cause triggers to be activated. A trigger might cause other statements to be executed or raise error conditions based on the insert values.
A named view with no WITH CHECK OPTION clause in its definition can allow nonconforming rows to be inserted into the view's base table(s). However, these rows cannot be retrieved again using the view. Additionally, each non-null insert value must conform to any foreign and parent key relationships defined for target tables -- such that a value inserted into a column defined as a foreign key must equal some value in the primary key column of the parent table.
Unless appropriate locks already exist, one or more exclusive locks are acquired at the execution of a successful INSERT statement. Until a commit or rollback operation releases the locks, only the application process that performed the insert can access the inserted row. If LOBs are not inserted into the row, application processes that are running with uncommitted read can also access the inserted row. The locks can also prevent other application processes from performing operations on the table. However, application processes that are running with uncommitted read can access locked pages and rows.
An example of the INSERT command is:
INSERT INTO part
VALUES ("P7", "WHEEL", "WHITE", 2, "ATHENS")
This statement inserts P7, a two pound white wheel made in Athens, into table PART. To perform this operation correctly, you must enter the data in exactly the order it is to appear in the table. If the column list is specified, it determines the insertion sequence.
To insert a NULL (empty) value, enter NULL in the appropriate column location of the INSERT command statement. Any table column can record inserted NULL values, unless a CREATE or ALTER TABLE command originally defined the column as either NOT NULL or NOT NULL WITH DEFAULT. In the later case, NULL values can be inserted, but will not be recorded in the table (the default values for the column data type will be recorded instead). For example, if the weight and color of the part we are about to insert are not known, type:
INSERT INTO part
VALUES ("P7", "WHEEL", NULL, NULL, "ATHENS")
If a value list is not specified for the INSERT command, a SELECT command must be specified. The order of the columns selected must match the order in the column list specification. However, the number of columns selected does not need to match the number of columns defined in the column list. If the column list has more columns than the SELECT command, the extra columns will be assigned NULL values. If the column list has fewer columns than the SELECT command, the system will use only as many columns as are available.
The columns selected do not need to match the data type of the column list. They must, however, be type compatible. See the ALTER TABLE command for the definition of compatible conversions.
Try the following sequence of commands using the part-supplier database. First enter the command:
CREATE TABLE bluparts(pno CHAR 2, pname CHAR 5)
This creates a new table called BLUPARTS. Next enter the command:
INSERT INTO bluparts
(pno, pname)
SELECT pno, pname
FROM part
WHERE color = "blue"
Note that because the PART table has a different number of columns than the BLUPARTS table, you should be careful when specifying the columns selected from the PART table to insert into the BLUPARTS table.