INSERT statement

Inserts a single row or a selection of rows from elsewhere in the database into a table.

Syntax

Insert one or more rows

INSERT [ INTO ] [ owner.]table-name [ ( column-name, ... ) ]
[ ON EXISTING { 
   ERROR 
   | SKIP 
   | UPDATE [ DEFAULTS { ON | OFF } ] 
   } ]
{ DEFAULT VALUES 
   | VALUES row-value-constructor }
[ OPTION( query-hint [, ... ] ) ]

Insert the results of a SELECT statement

INSERT [ INTO ] [ owner.]table-name [ ( [ column-name [, ... ] ] ) ]
[ ON EXISTING { 
   ERROR 
   | SKIP 
   | UPDATE [ DEFAULTS { ON | OFF } ] 
   } ]
[ WITH AUTO NAME ]
select-statement
[ OPTION( query-hint[, ... ] ) ]

query-hint :
MATERIALIZED VIEW OPTIMIZATION option-value
| FORCE OPTIMIZATION
| FORCE NO OPTIMIZATION
| option-name = option-value

option-name :
 identifier

option-value :
 hostvar (indicator allowed)
| string
| identifier
| number

insert-expression :
 expression | DEFAULT

row-value-constructor :
 ( [ insert-expression [, ... ] ] ) [, ( [ insert-expression [, ... ] ] ) ... ]

Parameters

VALUES clause
Use the VALUES clause to specify the values to insert. To insert the default values defined for the columns, specify DEFAULT VALUES. You can also specify VALUES ( ), which is equivalent to DEFAULT VALUES. The VALUES clause also support row value constructors so that you can insert multiple rows of values in a single statement. The number and order of insert-expression values in each row-value-constructor must correspond to the column list specified in the INTO clause. If a column list is not specified, it is assumed to be the complete ordered column list for the table. If you specify an empty column list (), then each of the columns in the table must have a default value. If you specify multiple row value constructors, they must be separated by commas.

If an error occurs while inserting any of the rows, all of the changes are rolled back.
WITH AUTO NAME clause
WITH AUTO NAME applies only to syntax 2. If you specify WITH AUTO NAME, the names of the items in the query block determine which column the data belongs in. The query block items should be either column references or aliased expressions. Destination columns not defined in the query block are assigned their default value. This is useful when the number of columns in the destination table is very large.

The INSERT statement returns an error if the WITH AUTO NAME clause is specified and the query block contains columns that do not match columns in the target table. For example, executing the following statement returns an error indicating that the operation column in the SELECT query block cannot be found in the table:
```
CREATE TABLE MyTable5(
      pk INT PRIMARY KEY DEFAULT AUTOINCREMENT,
      TableName CHAR(128),
      TableNameLen INT );
INSERT 
INTO MyTable5 WITH AUTO NAME
SELECT length(t.table_name) AS TableNameLen,
      t.table_name AS TableName, 1 as operation
FROM SYS.SYSTAB t
WHERE table_id <= 10;
```
ON EXISTING clause
The ON EXISTING clause of the INSERT statement applies to both syntaxes. It updates existing rows in a table, based on primary key lookup, with new column values. This clause can only be used on tables that have a primary key. Attempting to use this clause on tables without primary keys generates a syntax error. You cannot insert values into a proxy table with the ON EXISTING clause.

Note If you anticipate many rows qualifying for the ON EXISTING condition, consider using the MERGE statement instead. The MERGE statement provides more control over the actions you can take for matching rows. It also provides a more sophisticated syntax for defining what constitutes a match.

If you specify the ON EXISTING clause, the database server performs a primary key lookup for each input row. If the corresponding row does not already exist in the table, it inserts the new row. For rows that already exist in the table, you can choose to silently ignore the input row (SKIP), generate an error message for duplicate key values (ERROR), or update the old values using the values from the input row (UPDATE). By default, if you do not specify the ON EXISTING clause, attempting to insert rows into a table where the row already exists results in a duplicate key value error, and is equivalent to specifying the ON EXISTING ERROR clause. Rows that are skipped are included in the @@rowcount variable.

When using the ON EXISTING UPDATE clause, the input row is compared to the stored row. Any column values explicitly stated in the input row replace the corresponding column values in the stored row. Likewise, column values not explicitly stated in the input row result in no change to the corresponding column values in the stored row, with the exception of columns with defaults. When using the ON EXISTING UPDATE clause with columns that have defaults (including DEFAULT AUTOINCREMENT columns), you can further specify whether to update the column value with the default values by specifying ON EXISTING UPDATE DEFAULTS ON, or leave the column value as it is by specifying ON EXISTING UPDATE DEFAULTS OFF. If nothing is specified, the default behavior is ON EXISTING UPDATE DEFAULTS OFF.

Note DEFAULTS ON and DEFAULTS OFF parameters do not affect values in DEFAULT TIMESTAMP, DEFAULT UTC TIMESTAMP, or DEFAULT LAST USER. For these columns, the value in the stored row is always updated during the UPDATE.

When using the ON EXISTING SKIP and ON EXISTING ERROR clauses, if the table contains default columns, the server computes the default values even for rows that already exist. As a result, default values such as AUTOINCREMENT cause side effects even for skipped rows. In this case of AUTOINCREMENT, this results in skipped values in the AUTOINCREMENT sequence. The following example illustrates this:
```
CREATE TABLE t1( c1 INT PRIMARY KEY, c2 INT DEFAULT AUTOINCREMENT );
INSERT INTO t1( c1 ) ON EXISTING SKIP VALUES( 20 );
INSERT INTO t1( c1 ) ON EXISTING SKIP VALUES( 20 );
INSERT INTO t1( c1 ) ON EXISTING SKIP VALUES( 30 );
```
The row defined in the first INSERT statement is inserted, and c2 is set to 1. The row defined in the second INSERT statement is skipped because it matches the existing row. However, the autoincrement counter still increments to 2 (but does not impact the existing row). The row defined in the third INSERT statement is inserted, and the value of c2 is set to 3. So, the values inserted for the example above are:
```
20,1
30,3
```
Caution
If you are using SQL Remote, do not replicate DEFAULT LAST USER columns. When the column is replicated the column value is set to the SQL Remote user, not the replicated value.
OPTION clause
Use this clause to specify hints for executing the statement. The setting you specify is only applicable to the current statement and takes precedence over any public or temporary option settings, including those set by ODBC-enabled applications. The following hints are supported:
- MATERIALIZED VIEW OPTIMIZATION option-value
- FORCE OPTIMIZATION
- FORCE NO OPTIMIZATION
- option-name = option-value.
  
  Use an OPTION( isolation_level = ... ) specification in the query text to override all other means of specifying isolation level for a query.
  
  Use an OPTION( parameterization_level = ... ) specification in the query text to override the parameterization level for a query.

Remarks

The INSERT statement is used to add new rows to a database table.

Since text indexes and materialized views are impacted by changes to the underlying table data, consider truncating dependent text indexes or materialized views before bulk loading (LOAD TABLE, INSERT, MERGE) data into their underlying tables.

Insert one or more rows
Insert a single row, or multiple rows, with the specified expression column values. Multiple rows, if specified, are delimited by additional parentheses. The keyword DEFAULT can be used to cause the default value for the column to be inserted. If the optional list of column names is given, values are inserted one for one into the specified columns. If the list of column names is not specified, the values are inserted into the table columns in the order they were created (the same order as retrieved with SELECT *). The row is inserted into the table at an arbitrary position. (In relational databases, tables are not ordered.)
Insert the results of a SELECT statement
Perform mass insertion into a table with the results of a fully general SELECT statement. Insertions are done in an arbitrary order unless the SELECT statement contains an ORDER BY clause.

If you specify column names, the columns from the SELECT list are matched ordinally with the columns specified in the column list, or sequentially in the order in which the columns were created.

Inserts can be done into views if the query specification defining the view is updatable.

Character strings inserted into tables are always stored in the same case as they are entered, regardless of whether the database is case sensitive. So, the string 'Value' inserted into a table is always held in the database with an uppercase V and the remainder of the letters lowercase. SELECT statements return the string as Value. If the database is not case sensitive, however, all comparisons make Value the same as value, VALUE, and so on. Further, if a single-column primary key already contains an entry Value, an INSERT of value is rejected, as it would make the primary key not unique.

Inserting a significant amount of data using the INSERT statement will also update column statistics.

Note To insert many rows into a table, it is more efficient to declare a cursor and insert the rows through the cursor, where possible, than to perform many separate INSERT statements. Before inserting data, you can specify the percentage of each table page that should be left free for later updates.

Privileges

You must be the owner of the table, or have the INSERT ANY TABLE privilege, or have INSERT privilege on the table. Additionally, if the ON EXISTING UPDATE clause is specified, you must have the UPDATE ANY TABLE system privilege, or have UPDATE privilege on the table.

Side effects

None.

Standards

ANSI/ISO SQL Standard
Core Feature. The DEFAULT VALUES clause is optional ANSI/ISO SQL Language Feature F222, "INSERT statement: DEFAULT VALUES clause". Support for row value constructors in an INSERT statement comprises part of optional ANSI/ISO SQL Language Feature F641, "Row and table constructors". The VALUES keyword required by the software to specify the values to be inserted is not part of the standard.

The following extensions are also not part of the ANSI/ISO SQL Standard but are supported in the software:
- The INSERT...ON EXISTING clause is not in the standard. An ANSI/ISO SQL compliant equivalent in many instances is the MERGE statement.
- The OPTION clause.
- The WITH AUTO NAME clause.

Example

Add an Eastern Sales department to the database.

INSERT INTO GROUPO.Departments ( DepartmentID, DepartmentName )
VALUES ( 230, 'Eastern Sales' );

Create the table DepartmentHead and fill it with the names of department heads and their departments using the WITH AUTO NAME syntax.

CREATE TABLE DepartmentHead(
      pk INT PRIMARY KEY DEFAULT AUTOINCREMENT,
      DepartmentName VARCHAR(128),
      ManagerName VARCHAR(128) );
INSERT
INTO DepartmentHead WITH AUTO NAME
SELECT GivenName || ' ' || Surname AS ManagerName,
      DepartmentName
FROM GROUPO.Employees JOIN GROUPO.Departments
ON EmployeeID = DepartmentHeadID;

Create the table MyTable6 and populate it using the WITH AUTO NAME syntax.

CREATE TABLE MyTable6(
      pk INT PRIMARY KEY DEFAULT AUTOINCREMENT,
      TableName CHAR(128),
      TableNameLen INT );
INSERT INTO MyTable6 WITH AUTO NAME
SELECT
      length(t.table_name) AS TableNameLen,
      t.table_name AS TableName
FROM SYS.SYSTAB t
WHERE table_id <= 10;

Insert a new department, executing the statement at isolation level 3, rather than using the current isolation level setting of the database.

INSERT INTO GROUPO.Departments
   (DepartmentID, DepartmentName, DepartmentHeadID)
   VALUES(600, 'Foreign Sales', 129)
   OPTION( isolation_level = 3 );

The following example inserts three rows into a fictitious table, T:

INSERT INTO T (c1,c2,c3)
VALUES (1,10,100), (2,20,200), (3,30,300);

This example inserts three rows into a fictitious table, T, of four columns where each column has a default value defined:

INSERT INTO T ()
VALUES (), (), ();