Inserting rows using a select-statement

You can use a select-statement within an INSERT statement to insert zero, one, or more rows into a table from the result table of the select-statement.

One use for this kind of INSERT statement is to move data into a table you created for summary data. For example, suppose you want a table that shows each employee's time commitments to projects. Create a table called EMPTIME with the columns EMPNUMBER, PROJNUMBER, STARTDATE, and ENDDATE and then use the following INSERT statement to fill the table: 

  INSERT INTO CORPDATA.EMPTIME
     (EMPNUMBER, PROJNUMBER, STARTDATE, ENDDATE)
  SELECT EMPNO, PROJNO, EMSTDATE, EMENDATE
    FROM CORPDATA.EMPPROJACT

The select-statement embedded in the INSERT statement is no different from the select-statement you use to retrieve data. With the exception of FOR READ ONLY, FOR UPDATE, or the OPTIMIZE clause, you can use all the keywords, functions, and techniques used to retrieve data. SQL inserts all the rows that meet the search conditions into the table you specify. Inserting rows from one table into another table does not affect any existing rows in either the source table or the target table.

You should consider the following when inserting multiple rows into a table:

Notes:
  1. The number of columns implicitly or explicitly listed in the INSERT statement must equal the number of columns listed in the select-statement.
  2. The data in the columns you are selecting must be compatible with the columns you are inserting into when using the INSERT with select-statement.
  3. In the event the select-statement embedded in the INSERT returns no rows, an SQLCODE of 100 is returned to alert you that no rows were inserted. If you successfully insert rows, the SQLERRD(3) field of the SQLCA has an integer representing the number of rows SQL actually inserted. This value is also available from the ROW_COUNT diagnostics item in the GET DIAGNOSTICS statement.
  4. If SQL finds an error while running the INSERT statement, SQL stops the operation. If you specify COMMIT (*CHG), COMMIT(*CS), COMMIT (*ALL), or COMMIT(*RR), nothing is inserted into the table and a negative SQLCODE is returned. If you specify COMMIT(*NONE), any rows inserted before the error remain in the table.