Inserting rows using the INSERT statement

To add a single row or multiple rows to a table or view, use a form of the INSERT statement.

You can use the INSERT statement to add new rows to a table or view in one of the following ways:

For every row you insert, you must supply a value for each column defined with the NOT NULL attribute if that column does not have a default value. The INSERT statement for adding a row to a table or view may look like this:

   INSERT INTO table-name
      (column1, column2, ... )
   VALUES (value-for-column1, value-for-column2, ... )

The INTO clause names the columns for which you specify values. The VALUES clause specifies a value for each column named in the INTO clause. The value you specify can be:

You must provide a value in the VALUES clause for each column named in an INSERT statement's column list. The column name list can be omitted if all columns in the table have a value provided in the VALUES clause. If a column has a default value, the keyword DEFAULT may be used as a value in the VALUES clause. This causes the default value for the column to be placed in the column.

It is a good idea to name all columns into which you are inserting values because:

If the column is defined to allow null values or to have a default, you do not need to name it in the column name list or specify a value for it. The default value is used. If the column is defined to have a default value, the default value is placed in the column. If DEFAULT was specified for the column definition without an explicit default value, SQL places the default value for that data type in the column. If the column does not have a default value defined for it, but is defined to allow the null value (NOT NULL was not specified in the column definition), SQL places the null value in the column.

When your program attempts to insert a row that duplicates another row already in the table, an error might occur. Multiple null values may or may not be considered duplicate values, depending on the option used when the index was created.

If SQL finds an error while running the INSERT statement, it stops inserting data. If you specify COMMIT(*ALL), COMMIT(*CS), COMMIT(*CHG), or COMMIT(*RR), no rows are inserted. Rows already inserted by this statement, in the case of INSERT with a select-statement or blocked insert, are deleted. If you specify COMMIT(*NONE), any rows already inserted are not deleted.

A table created by SQL is created with the Reuse Deleted Records parameter of *YES. This allows the database manager to reuse any rows in the table that were marked as deleted. The CHGPF command can be used to change the attribute to *NO. This causes INSERT to always add rows to the end of the table.

The order in which rows are inserted does not guarantee the order in which they will be retrieved.

If the row is inserted without error, the SQLERRD(3) field of the SQLCA has a value of 1.

Note: For blocked INSERT or for INSERT with select-statement, more than one row can be inserted. The number of rows inserted is reflected in SQLERRD(3) in the SQLCA. It is also available from the ROW_COUNT diagnostics item in the GET DIAGNOSTICS statement.