INSERT

The INSERT statement adds new rows to a table. You can insert one row of literal values, or many rows at once from a SELECT.

INSERT syntax

• Basic shape: INSERT INTO table (columns) VALUES (values).
• The column list is optional, but always code it. It protects the program when columns are added later.
• The number of values must equal the number of columns, or DB2 gives SQLCODE -117.
• Columns left out of the list get NULL, or their DEFAULT value if one is defined.

INSERT with SELECT

• INSERT INTO ... SELECT copies rows from another table or query in one statement.
• Useful for loading backup tables and archiving old rows.
• The SELECT's column count and types must match the INSERT column list.

INSERT rules to remember

• NOT NULL columns must get a value in every INSERT.
• Primary key values must be unique, or the INSERT fails with SQLCODE -803.
• Foreign key values must exist in the parent table, or the INSERT fails with SQLCODE -530.
• Inserted rows stay locked until COMMIT, so commit in batches during large loads.

Example

• Inserting one employee row and copying rows into a backup table:-