Loading data by issuing INSERT statements

You can load data into tables is by issuing INSERT statements interactively or by embedding them in application programs.

Procedure

To load data into Db2 tables by issuing the INSERT statement, use one of the following approaches:

Issue an INSERT statements to specify a single data row. The simplest form of the INSERT statement inserts a single row of data. In this form of the statement, you specify the table name, the columns into which the data is to be inserted, and the data itself.
For example, suppose that you create a test table, TEMPDEPT, with the same characteristics as the department table:
```
CREATE TABLE SMITH.TEMPDEPT
  (DEPTNO  CHAR(3)  NOT NULL,
  DEPTNAME VARCHAR(36)   NOT NULL,
  MGRNO    CHAR(6)  NOT NULL,
  ADMRDEPT CHAR(3)  NOT NULL)
  IN DSN8D91A.DSN8S91D;
```
To now add a row to table TEMPDEPT, you can issue the following statement:
```
INSERT INTO SMITH.TEMPDEPT
  VALUES ('X05','EDUCATION','000631','A01');
```
Load multiple rows into a table with a single INSERT statement. You can use one of the following approaches:
- Issue the fullselect form of INSERT statement to insert rows selected from another table. For example, the following statement loads TEMPDEPT with data from the department table about all departments that report to department D01.
```
INSERT INTO SMITH.TEMPDEPT
  SELECT DEPTNO,DEPTNAME,MGRNO,ADMRDEPT
  FROM DSN8910.DEPT
  WHERE ADMRDEPT='D01';
```
- Embed the FOR n ROWS form of INSERT statement in an application program to insert multiple rows into a table from the values that are provided in host-variable arrays, where each array corresponds to a column. The INSERT statement specifies the table name, the columns into which the data is to be inserted, and the arrays that contain the data.
  
  For example, you can load TEMPDEPT with the number of rows in the host variable num-rows by using the following embedded INSERT statement:
```
EXEC SQL
  INSERT INTO SMITH.TEMPDEPT
  VALUES (:hva1, :hva2, :hva3, :hva4)
  FOR :num-rows ROWS;
```
  Assume that the host-variable arrays hva1, hva2, hva3, and hva4 are populated with the values that are to be inserted. The number of rows to insert must be less than or equal to the dimension of each host-variable array. For more information, see Using host-variable arrays in SQL statements.

What to do next

If you plan to use the INSERT statement to load tables, you should consider the following the implications of doing so:

If you are inserting a large number of rows, you can also use the LOAD utility. Alternatively, use multiple INSERT statements with predicates that isolate the data that is to be loaded, and then commit after each insert operation.
When a table, whose indexes are already defined, is populated by using the INSERT statement, both the FREEPAGE and the PCTFREE parameters are ignored. FREEPAGE and PCTFREE are in effect only during a LOAD or REORG operation. For more information, see Reserving free spaces for indexes.
Set the NOT LOGGED attribute for table spaces when large volumes of data are being inserted with parallel INSERT processes. If the data in the table space is lost or damaged, it can be reinserted from its original source.
You can load a value for a ROWID column with an INSERT and fullselect only if the ROWID column is defined as GENERATED BY DEFAULT. If you have a table with a column that is defined as ROWID GENERATED ALWAYS, you can propagate non-ROWID columns from a table with the same definition. For more information, see Rules for inserting data into a ROWID column.
You cannot use an INSERT statement on system-maintained materialized query tables.
REBUILD-pending (RBDP) status is set on a data-partitioned secondary index if you create the index after you insert a row into a table. In addition, the last partition of the table space is set to REORG-pending (REORP) restrictive status.
When you insert a row into a table that resides in a partitioned table space and the value of the first column of the limit key is null, the result of the INSERT depends on whether Db2 enforces the limit key of the last partition:
- When Db2 enforces the limit key of the last partition, the INSERT fails (if the first column is ascending).
- When Db2 enforces the limit key of the last partition, the rows are inserted into the first partition (if the first column is descending).
- When Db2 does not enforce the limit key of the last partition, the rows are inserted into the last partition (if the first column is ascending) or the first partition (if the first column is descending).
Db2 enforces the limit key of the last partition for the following table spaces:
- Tables spaces using table-controlled partitioning that are large or non-large (any DSSIZE)
- Table spaces using table-controlled or index-controlled partitioning that are large (DSSIZE 4 GB or greater)