Inserting Db2 data by using INSERT statements

You can use an INSERT statement to add new rows to a table or view.

The INSERT statement has the following three forms:

VALUES form

The VALUES form of the INSERT statement inserts a single row into the table or view using the values provided or referenced.

fullselect form

The fullselect form of the INSERT statement inserts one or more rows into the table or view using values from other tables, or views, or both.

FOR n ROWS form

The FOR n ROWS form of the INSERT statement inserts multiple rows into the table or view using values provided or referenced. Although not required, the values can come from host-variable arrays.

This form of INSERT is supported in SQL procedure applications. However, because host-variable arrays are not supported in SQL procedure applications, the support is limited to insertion of scalar values.

You can add new data to an existing table in other ways, too. You might need to add large amounts of data to an existing table. Some efficient options include copying a table into another table, writing an application program that enters data into a table, and using the Db2 LOAD utility to enter data.

Example INSERT statements

Begin general-use programming interface information.

Suppose that you want to add a new row to the NEWDEPT table. You can use the following INSERT statement.
```
 INSERT INTO NEWDEPT (DEPTNO, DEPTNAME, MGRNO, ADMRDEPT)
   VALUES ('E31', 'PUBLISHING', '000020', 'D11'); 
```
After inserting the new department row into the NEWDEPT table, you can use the following SELECT statement to see the modified table.
```
SELECT *
  FROM NEWDEPT
  WHERE DEPTNO LIKE 'E%'
  ORDER BY DEPTNO;
```
The result table gives you the new department row that you inserted for department E31 and any existing departments with department numbers that begin with the letter E.
```
DEPTNO     DEPTNAME             MGRNO      ADMRDEPT
======     ================     ======     ========
E21        SOFTWARE SUPPORT     ------          D11
E31        PUBLISHING           000020          D11      
```

The following statement inserts values into a single row of the DSN8D10.EMP sample table.

   INSERT INTO DSN8D10.EMP
     VALUES ('000205','MARY','T','SMITH','D11','2866',
              '1981-08-10','ANALYST',16,'F','1956-05-22',
             16345,500,2300);

Assume that SMITH.TEMPEMPL is a created temporary table. The following statement populates the table with data from sample table DSN8D10.EMP.
```
   INSERT INTO SMITH.TEMPEMPL
     SELECT *
     FROM DSN8D10.EMP;
```
Assume that SESSION.TEMPEMPL is a declared temporary table. The following statement populates the table with data from department D11 in sample table DSN8D10.EMP.
```
   INSERT INTO SESSION.TEMPEMPL
     SELECT *
     FROM DSN8D10.EMP
     WHERE WORKDEPT='D11';
```
The following statement inserts a row into sample table DSN8D10.EMP_PHOTO_RESUME. Set the value for column EMPNO to the value in host variable HV_ENUM. Let the value for column EMP_ROWID be generated because it was defined with a row ID data type and with clause GENERATED ALWAYS.
```
   INSERT INTO DSN8D10.EMP_PHOTO_RESUME(EMPNO, EMP_ROWID)
     VALUES (:HV_ENUM, DEFAULT);
```
You can only insert user-specified values into ROWID columns that are defined as GENERATED BY DEFAULT and not as GENERATED ALWAYS. Therefore, in the above example, if you were to try to insert a value into EMP_ROWID instead of specifying DEFAULT, the statement would fail unless you also specify OVERRIDING USER VALUE. For columns that are defined as GENERATED ALWAYS, the OVERRIDING USER VALUE clause causes Db2 to ignore any user-specified value and generate a value instead.

For example, assume that you want to copy the rows in DSN8D10.EMP_PHOTO_RESUME to another table that has a similar definition (both tables have a ROWID columns defined as GENERATED ALWAYS). For the following INSERT statement, the OVERRIDING USER VALUE clause causes Db2 to ignore the EMP_ROWID column values from DSN8D10.EMP_PHOTO_RESUME and generate values for the corresponding ROWID column in B.EMP_PHOTO_RESUME.
```
   INSERT INTO B.EMP_PHOTO_RESUME
     OVERRIDING USER VALUE
     SELECT * FROM DSN8D10.EMP_PHOTO_RESUME;
```
Assume that the T1 table has one column. The following statement inserts a variable (:hv) number of rows of data into the T1 table. The values to be inserted are provided in a host-variable array (:hva). In this example, :hva represents the host-variable array and :hvind represents the array of indicator variables.
```
EXEC SQL INSERT INTO T1 VALUES (:hva:hvind) FOR :hv ROWS ATOMIC;
```

Assume that the T2 table has 2 columns, C1 is a SMALL INTEGER column, and C2 is an INTEGER column. The following statement inserts 10 rows of data into the T2 table. The values to be inserted are provided in host-variable arrays :hva1 (an array of INTEGERS) and :hva2 (an array of DECIMAL(15,0) values). The data values for :hva1 and :hva2 are represented in the following table:

Table 1. Data values for :hva1 and :hva2
Array entry	:hva1	:hva2
1	1	32768
2	-12	90000
3	79	2
4	32768	19
5	8	36
6	5	24
7	400	36
8	73	4000000000
9	-200	2000000000
10	35	88

EXEC SQL INSERT INTO T2 (C1, C2) 
  VALUES (:hva1:hvind1, :hva2:hvind2) FOR 10 ROWS 
  NOT ATOMIC CONTINUE ON SQLEXCEPTION;

After execution of the INSERT statement, the following information will be in the SQLCA:

SQLCODE = -253
SQLSTATE = 22529
SQLERRD3 = 8

Although an attempt was made to insert 10 rows, only 8 rows of data were inserted. Processing continued after the first failed insert because NOT ATOMIC CONTINUE ON SQLEXCEPTION was specified. You can use the GET DIAGNOSTICS statement to find further information, for example.

The result of this statement is num_rows = 8 and num_cond = 2 (2 conditions).

GET DIAGNOSTICS :num_rows = ROW_COUNT, :num_cond = NUMBER;

The result of this statement is sqlstate = 22003, sqlcode = -302, and row_num = 4.

GET DIAGNOSTICS CONDITION 2 :sqlstate = RETURNED_SQLSTATE, 
                            :sqlcode = DB2_RETURNED_SQLCODE, 
                            :row_num = DB2_ROW_NUMBER;

The result of this statement is sqlstate = 22003, sqlcode = -302, and row_num = 8.

GET DIAGNOSTICS CONDITION 1 :sqlstate = RETURNED_SQLSTATE, 
                            :sqlcode = DB2_RETURNED_SQLCODE, 
                            :row_num = DB2_ROW_NUMBER;

Assume the above table T2 with two columns. C1 is a SMALL INTEGER column, and C2 is an INTEGER column. The following statement inserts 8 rows of data into the T2 table. The values to be inserted are provided in host-variable arrays :hva1 (an array of INTEGERS) and :hva2 (an array of DECIMAL(15,0) values.) The data values for :hva1 and :hva2 are represented in Table 1.
```
EXEC SQL INSERT INTO T2 (C1, C2)
  VALUES (:hva1:hvind1, :hva2:hvind2) FOR 8 ROWS
  NOT ATOMIC CONTINUE ON SQLEXCEPTION;
```
After execution of the INSERT statement, the following information will be in the SQLCA:
```
SQLCODE = -253
SQLSTATE = 22529
SQLERRD3 = 6
```
Although an attempt was made to insert 8 rows, only 6 rows of data were inserted. Processing continued after the first failed insert because NOT ATOMIC CONTINUE ON SQLEXCEPTION was specified. You can use the GET DIAGNOSTICS statement to find further information. For example, the result of the following statement is num_rows = 68 and num_cond = 2 (2 conditions).
```
GET DIAGNOSTICS :num_rows = ROW_COUNT, :num_cond = NUMBER;
```
The result of the following statement is sqlstate = 22003, sqlcode = -302, and row_num = 4.
```
GET DIAGNOSTICS CONDITION 2 :sqlstate = RETURNED_SQLSTATE, 
                            :sqlcode = DB2_RETURNED_SQLCODE, 
                            :row_num = DB2_ROW_NUMBER;
```
The result of the following statement is sqlstate = 22003, sqlcode = -302, and row_num = 8.
```
GET DIAGNOSTICS CONDITION 1 :sqlstate = RETURNED_SQLSTATE, 
                            :sqlcode = DB2_RETURNED_SQLCODE, 
                            :row_num = DB2_ROW_NUMBER;
```
Assume that table T1 has two columns. Insert a variable number (:hvn) or rows into T1. The values to be inserted are in host-variable arrays :hva and :hvb. In this example, the INSERT statement is contained within the SELECT statement of cursor CS1. The SELECT statement makes use of two other input host variables (:hv1 and :hv2) in the WHERE clause. Either a static or dynamic INSERT statement can be used.
```
-- Static INSERT statement:
DECLARE CS1 CURSOR WITH ROWSET POSITIONING FOR
   SELECT *
      FROM FINAL TABLE
         (INSERT INTO T1 VALUES (:hva, :hvb) FOR :hvn ROWS)
      WHERE C1 > :hv1 AND C2 < :hv2;
OPEN CS1;
-- Dynamic INSERT statement:
PREPARE INSSTMT FROM
   'SELECT *
       FROM FINAL TABLE
          (INSERT INTO T1 VALUES ( ? , ? ) FOR ? ROWS)
       WHERE C1 > ? AND C2 < ?';
DECLARE CS1 CURSOR WITH ROWSET POSITIONING FOR :INSSTMT;
OPEN CS1 USING :hva, :hvb, :hvn, :hv1, :hv2; (or OPEN CS1 USING DESCRIPTOR ...)
```
If the host-variable arrays for the multiple-row INSERT statement were to be specified using a descriptor, that descriptor (SQLDA) would have to describe all input host variables in the statement, and the order of the entries in the SQLDA should be the same as the order of the order of the host variables, host-variable arrays, and values for the FOR n ROWS clauses in the statement. For example, given the statement above, the SQLVAR entries in the descriptor must be assigned in the following order: :hvn, :hva, :hvb, :hv1, hv2. Also, the SQLVAR entries for host-variable arrays must be tagged in the SQLDA as column arrays (by specifying a special value in part of the SQLNAME field for a host variable), and the SQLVAR entry for the number of rows value must be tagged in the SQLDA (by specifying another special value in part of the SQLNAME field for the host variable).

Insert a row into table T1. The row contains the value 'xyz' for column COL1, and the cardinality of array INTA for column COL2.

CREATE TYPE INTARRAY AS INTEGER ARRAY [6];
CREATE VARIABLE INTA AS INTARRAY;
SET INTA = ARRAY [ 1, 2, 3, 4, 5 ];
CREATE TABLE T1 (COL1 CHAR(7), COL2 INT);
INSERT INTO T1 VALUES ('xyz', CARDINALITY(INTA));

Insert the values from arrays CHARA and INTA into table T1. For a row of T1, a value of the CHARA array is used for column COL1, and the value of the INTA array with the same array index is used for column COL2.

CREATE TYPE INTARRAY AS INTEGER ARRAY[10];
CREATE TYPE CHARARRAY AS CHAR(7) ARRAY[10];
CREATE VARIABLE INTA AS INTARRAY;
CREATE VARIABLE CHARA AS CHARARRAY;
SET INTA = ARRAY[1, 2, 3, 4, 5];
SET CHARA = ARRAY['a', 'b', 'c', 'd'];
CREATE TABLE T1 (COL1 CHAR(7), COL2 INT);
INSERT INTO T1
 SELECT *
 FROM UNNEST(CHARA, INTA) AS (COL1, COL2);

Insert three rows of data into table T1. For each inserted row, assign the value of the tenth element in the INTA array variable to the COL1 column.

CREATE TYPE INTARRAY AS INTEGER ARRAY[10];
CREATE VARIABLE INTA AS INTARRAY;
CREATE VARIABLE VAR1 AS INTEGER;
CREATE VARIABLE VAR2 AS INTEGER;
SET INTA = ARRAY[10, 20, 30, 40, 50, 60, 70, 80, 90, 100];
CREATE TABLE T1 (COL1 INT, COL2 CHAR(10));
SET VAR1 = 10;
SET VAR2 = 3;
-- Perform a multiple row insert (specifying a FOR n ROWS clause).
-- The value to be inserted is specified by a reference to an array element.
INSERT INTO T1 (COL1) VALUES(INTA[VAR1]) FOR VAR2 ROWS;

The result of the these operations is that a value of 100 is assigned to column COL1 for three rows.

End general-use programming interface information.