Inserting rows by using the INSERT statement

One way to insert data into tables is to use the SQL INSERT statement. This method is useful for inserting small amounts of data or inserting data from another table or view.

About this task

Use an INSERT statement to add new rows to a table or view. Using an INSERT statement, you can do the following actions:

Specify the column values to insert a single row. You can specify constants, host variables, expressions, DEFAULT, or NULL by using the VALUES clause.
In an application program, specify arrays of column values to insert multiple rows into a table. Use host variable arrays in the VALUES clause of the INSERT FOR n ROWS statement to add multiple rows of column values to a table.
Include a SELECT statement in the INSERT statement to tell DB2® that another table or view contains the data for the new row or rows.

In each case, for every row that you insert, you must provide a value for any column that does not have a default value. For a column that meets one of the following conditions, specify DEFAULT to tell DB2 to insert the default value for that column:

The column is nullable.
The column is defined with a default value.
The column has data type ROWID. ROWID columns always have default values.
The column is an identity column. Identity columns always have default values.
The column is a row change timestamp column.

The values that you can insert into a ROWID column, an identity column, or a row change timestamp column depend on whether the column is defined with GENERATED ALWAYS or GENERATED BY DEFAULT.

Inserting a single row:

You can use the VALUES clause of the INSERT statement to insert a single row of column values into a table. You can either name all of the columns for which you are providing values, or you can omit the list of column names. If you omit the column name list, you must specify values for all of the columns.

Recommendation: For static INSERT statements, name all of the columns for which you are providing values for the following reasons:

Your INSERT statement is independent of the table format. (For example, you do not need to change the statement when a column is added to the table.)
You can verify that you are specifying the values in order.
Your source statements are more self-descriptive.

If you do not name the columns in a static INSERT statement, and a column is added to the table, an error can occur if the INSERT statement is rebound. An error will occur after any rebind of the INSERT statement unless you change the INSERT statement to include a value for the new column. This is true even if the new column has a default value.

When you list the column names, you must specify their corresponding values in the same order as in the list of column names.

Example: The following statement inserts information about a new department into the YDEPT table.

INSERT INTO YDEPT (DEPTNO, DEPTNAME, MGRNO, ADMRDEPT, LOCATION)
  VALUES ('E31', 'DOCUMENTATION', '000010', 'E01', ' ');

After inserting a new department row into your YDEPT table, you can use a SELECT statement to see what you have loaded into the table. The following SQL statement shows you all of the new department rows that you have inserted:

SELECT *
  FROM YDEPT
  WHERE DEPTNO LIKE 'E%'
  ORDER BY DEPTNO;

The result table looks similar to the following output:

DEPTNO  DEPTNAME                              MGRNO   ADMRDEPT  LOCATION
======  ====================================  ======  ========  ===========
E01     SUPPORT SERVICES                      000050  A00       -----------
E11     OPERATIONS                            000090  E01       -----------
E21     SOFTWARE SUPPORT                      000100  E01       -----------
E31     DOCUMENTATION                         000010  E01       -----------

Example: The following statement inserts information about a new employee into the YEMP table. Because the WORKDEPT column is a foreign key, the value that is inserted for that column (E31) must be a value in the primary key column, which is DEPTNO in the YDEPT table.

INSERT INTO YEMP
  VALUES ('000400', 'RUTHERFORD', 'B', 'HAYES', 'E31', '5678', '1998-01-01',
          'MANAGER', 16, 'M', '1970-07-10', 24000, 500, 1900);

Example: The following statement also inserts a row into the YEMP table. Because the unspecified columns allow null values, DB2 inserts null values into the columns that you do not specify.

INSERT INTO YEMP
  (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, JOB)
  VALUES ('000410', 'MILLARD', 'K', 'FILLMORE', 'D11', '4888', 'MANAGER');

Parent topic: Inserting data into tables

Related concepts:

Rules for inserting data into an identity column

Rules for inserting data into a ROWID column

Inserting rows into a table from another table

Related reference:

CREATE TABLE