Selecting values while inserting data

Examples

In addition to examples that use the Db2 sample tables, the examples in this topic use an EMPSAMP table that has the following definition:

CREATE TABLE EMPSAMP
  (EMPNO     INTEGER GENERATED ALWAYS AS IDENTITY,
   NAME      CHAR(30),
   SALARY    DECIMAL(10,2),
   DEPTNO    SMALLINT,
   LEVEL     CHAR(30),
   HIRETYPE  VARCHAR(30) NOT NULL WITH DEFAULT 'New Hire',
   HIREDATE  DATE NOT NULL WITH DEFAULT);

Example 1: Retrieving generated column values

Assume that you need to insert a row for a new employee into the EMPSAMP table. To find out the values for the generated EMPNO, HIRETYPE, and HIREDATE columns, use the following SELECT FROM INSERT statement:

SELECT EMPNO, HIRETYPE, HIREDATE
  FROM FINAL TABLE (INSERT INTO EMPSAMP (NAME, SALARY, DEPTNO, LEVEL)
                    VALUES('Mary Smith', 35000.00, 11, 'Associate'));

The SELECT statement returns the Db2-generated identity value for the EMPNO column, the default value 'New Hire' for the HIRETYPE column, and the value of the CURRENT DATE special register for the HIREDATE column.

Recommendation: Use the SELECT FROM INSERT statement to insert a row into a parent table and retrieve the value of a primary key that was generated by Db2 (a ROWID or identity column). In another INSERT statement, specify this generated value as a value for a foreign key in a dependent table.

Example 2: Retrieving values updated by triggers

Suppose that a BEFORE INSERT trigger is created on table EMPSAMP to give all new employees at the Associate level a $5000 increase in salary. The trigger has the following definition:

CREATE TRIGGER NEW_ASSOC
  NO CASCADE BEFORE INSERT ON EMPSAMP
  REFERENCING NEW AS NEWSALARY
  FOR EACH ROW MODE DB2SQL
    WHEN (NEWSALARY.LEVEL = 'ASSOCIATE')
    BEGIN ATOMIC
      SET NEWSALARY.SALARY = NEWSALARY.SALARY + 5000.00;
    END;

The INSERT statement in the FROM clause of the following SELECT statement inserts a new employee into the EMPSAMP table:

SELECT NAME, SALARY
  FROM FINAL TABLE (INSERT INTO EMPSAMP (NAME, SALARY, LEVEL)
                    VALUES('Mary Smith', 35000.00, 'Associate'));

The SELECT statement returns a salary of 40000.00 for Mary Smith instead of the initial salary of 35000.00 that was explicitly specified in the INSERT statement.

Selecting values when you insert a single row:

When you insert a new row into a table, you can retrieve any column in the result table of the SELECT FROM INSERT statement. When you embed this statement in an application, you retrieve the row into host variables by using the SELECT ... INTO form of the statement.

Example 4: Retrieving all values for a row inserted intro a structure.

You can retrieve all the values for a row that is inserted into a structure. For example, in the following statement :empstruct is a host variable structure that is declared with variables for each of the columns in the EMPSAMP table.

EXEC SQL SELECT * INTO :empstruct
  FROM FINAL TABLE (INSERT INTO EMPSAMP (NAME, SALARY, DEPTNO, LEVEL)
                    VALUES('Mary Smith', 35000.00, 11, 'Associate'));

Example 4: Selecting values when inserting data into a view

If the INSERT statement references a view that is defined with a search condition, that view must be defined with the WITH CASCADED CHECK OPTION option. When you insert data into the view, the result table of the SELECT FROM INSERT statement includes only rows that satisfy the view definition.

Because view V1 is defined with the WITH CASCADED CHECK OPTION option, you can reference V1 in the INSERT statement:

CREATE VIEW V1 AS
  SELECT C1, I1 FROM T1 WHERE I1 > 10
  WITH CASCADED CHECK OPTION;

SELECT C1 FROM
  FINAL TABLE (INSERT INTO V1 (I1) VALUES(12));

The value 12 satisfies the search condition of the view definition, and the result table consists of the value for C1 in the inserted row.

If you use a value that does not satisfy the search condition of the view definition, the insert operation fails, and Db2 returns an error.

Example 5: Selecting ROWID values when inserting multiple rows

In an application program, to retrieve values from the insertion of multiple rows, declare a cursor so that the INSERT statement is in the FROM clause of the SELECT statement of the cursor.

To see the values of the ROWID columns that are inserted into the employee photo and resume table, you can declare the following cursor:

EXEC SQL DECLARE CS1 CURSOR FOR
  SELECT EMP_ROWID
  FROM FINAL TABLE (INSERT INTO DSN8D10.EMP_PHOTO_RESUME (EMPNO)
                    SELECT EMPNO FROM DSN8D10.EMP);

Example 6: Using the FETCH FIRST clause

To see only the first five rows that are inserted into the employee photo and resume table, use the FETCH FIRST clause:

EXEC SQL DECLARE CS2 CURSOR FOR
  SELECT EMP_ROWID
  FROM FINAL TABLE (INSERT INTO DSN8D10.EMP_PHOTO_RESUME (EMPNO)
                    SELECT EMPNO FROM DSN8D10.EMP)
  FETCH FIRST 5 ROWS ONLY;

Example 7: Using the INPUT SEQUENCE clause

To retrieve rows in the order in which they are inserted, use the INPUT SEQUENCE clause:

EXEC SQL DECLARE CS3 CURSOR FOR
  SELECT EMP_ROWID
  FROM FINAL TABLE (INSERT INTO DSN8D10.EMP_PHOTO_RESUME (EMPNO)
                    VALUES(:hva_empno)
                    FOR 5 ROWS)
  ORDER BY INPUT SEQUENCE;

The INPUT SEQUENCE clause can be specified only if an INSERT statement is in the FROM clause of the SELECT statement. In this example, the rows are inserted from an array of employee numbers.

Example 8: Inserting rows with multiple encoding CCSIDs

Suppose that you want to populate an ASCII table with values from an EBCDIC table and then see selected values from the ASCII table. You can use the following cursor to select the EBCDIC columns, populate the ASCII table, and then retrieve the ASCII values:

EXEC SQL DECLARE CS4 CURSOR FOR
  SELECT C1, C2
  FROM FINAL TABLE (INSERT INTO ASCII_TABLE
                    SELECT * FROM EBCDIC_TABLE);

Example 9: Selecting additional columns when inserting data

You can use the INCLUDE clause to introduce a new column to the result table but not add a column to the target table.

Suppose that you need to insert department number data into the project table. Suppose also, that you want to retrieve the department number and the corresponding manager number for each department. Because MGRNO is not a column in the project table, you can use the INCLUDE clause to include the manager number in your result but not in the insert operation. The following SELECT FROM INSERT statement performs the insert operation and retrieves the data.

DECLARE CS1 CURSOR FOR
 SELECT manager_num, projname FROM FINAL TABLE
   (INSERT INTO PROJ (DEPTNO) INCLUDE(manager_num CHAR(6))
      SELECT DEPTNO, MGRNO FROM DEPT);

Example 10: Result table of the cursor when you insert multiple rows

In an application program, when you insert multiple rows into a table, you declare a cursor so that the INSERT statement is in the FROM clause of the SELECT statement of the cursor. The result table of the cursor is determined during OPEN cursor processing. The result table may or may not be affected by other processes in your application.

When you declare a scrollable cursor, the cursor must be declared with the INSENSITIVE keyword if an INSERT statement is in the FROM clause of the cursor specification. The result table is generated during OPEN cursor processing and does not reflect any future changes. You cannot declare the cursor with the SENSITIVE DYNAMIC or SENSITIVE STATIC keywords.

When you declare a non-scrollable cursor, any searched updates or deletes do not affect the result table of the cursor. The rows of the result table are determined during OPEN cursor processing.

For example, assume that your application declares a cursor, opens the cursor, performs a fetch, updates the table, and then fetches additional rows:

EXEC SQL DECLARE CS1 CURSOR FOR
  SELECT SALARY
  FROM FINAL TABLE (INSERT INTO EMPSAMP (NAME, SALARY, LEVEL)
                    SELECT NAME, INCOME, BAND FROM OLD_EMPLOYEE);
EXEC SQL OPEN CS1;
EXEC SQL FETCH CS1 INTO :hv_salary;
/* print fetch result */
...
EXEC SQL UPDATE EMPSAMP SET SALARY = SALARY + 500;
while (SQLCODE == 0) {
  EXEC SQL FETCH CS1 INTO :hv_salary;
  /* print fetch result */
  ...
}

The fetches that occur after the updates return the rows that were generated when the cursor was opened. If you use a simple SELECT (with no INSERT statement in the FROM clause), the fetches might return the updated values, depending on the access path that Db2 uses.

Example 11: Effect of WITH HOLD

When you declare a cursor with the WITH HOLD option and open the cursor, all of the rows are inserted into the target table. The WITH HOLD option has no effect on the SELECT FROM INSERT statement of the cursor definition. After your application performs a commit, you can continue to retrieve all of the inserted rows.

Assume that the employee table in the Db2 sample application has five rows. Your application declares a WITH HOLD cursor, opens the cursor, fetches two rows, performs a commit, and then fetches the third row successfully:

EXEC SQL DECLARE CS2 CURSOR WITH HOLD FOR
  SELECT EMP_ROWID
  FROM FINAL TABLE (INSERT INTO DSN8D10.EMP_PHOTO_RESUME (EMPNO)
                    SELECT EMPNO FROM DSN8D10.EMP);
EXEC SQL OPEN CS2;                                /* Inserts 5 rows */
EXEC SQL FETCH CS2 INTO :hv_rowid;   /* Retrieves ROWID for 1st row */
EXEC SQL FETCH CS2 INTO :hv_rowid;   /* Retrieves ROWID for 2nd row */
EXEC SQL COMMIT;                                  /* Commits 5 rows */
EXEC SQL FETCH CS2 INTO :hv_rowid;   /* Retrieves ROWID for 3rd row */

Example 12: Effect of SAVEPOINT and ROLLBACK

A savepoint is a point in time within a unit of recovery to which relational database changes can be rolled back. You can set a savepoint with the SAVEPOINT statement.

When you set a savepoint prior to opening the cursor and then roll back to that savepoint, all of the insertions are undone.

Assume that your application declares a cursor, sets a savepoint, opens the cursor, sets another savepoint, rolls back to the second savepoint, and then rolls back to the first savepoint:

EXEC SQL DECLARE CS3 CURSOR FOR
  SELECT EMP_ROWID
  FROM FINAL TABLE (INSERT INTO DSN8D10.EMP_PHOTO_RESUME (EMPNO)
                    SELECT EMPNO FROM DSN8D10.EMP);
EXEC SQL SAVEPOINT A ON ROLLBACK RETAIN CURSORS;       /* Sets 1st savepoint */ 
EXEC SQL OPEN CS3;
EXEC SQL SAVEPOINT B ON ROLLBACK RETAIN CURSORS;       /* Sets 2nd savepoint */
...
EXEC SQL ROLLBACK TO SAVEPOINT B;  /* Rows still in DSN8D10.EMP_PHOTO_RESUME */ 
...
EXEC SQL ROLLBACK TO SAVEPOINT A;            /* All inserted rows are undone */

Example 13: Errors during SELECT INTO processing

In an application program, when you insert one or more rows into a table by using the SELECT FROM INSERT statement, the result table of the insert operation may or may not be affected, depending on where the error occurred in the application processing.

If the insert processing or the select processing fails during a SELECT INTO statement, no rows are inserted into the target table, and no rows are returned from the result table of the insert operation. For example, assume that the employee table of the Db2 sample application has one row, and that the SALARY column has a value of 9999000.00.

EXEC SQL SELECT EMPNO INTO :hv_empno
  FROM FINAL TABLE (INSERT INTO EMPSAMP (NAME, SALARY)
                    SELECT FIRSTNAME || MIDINIT || LASTNAME,
                           SALARY + 10000.00 
                    FROM DSN8D10.EMP)

The addition of 10000.00 causes a decimal overflow to occur, and no rows are inserted into the EMPSAMP table.

Example 14: Errors during OPEN cursor processing

If the insertion of any row fails during the OPEN cursor processing, all previously successful insertions are undone. The result table of the insert is empty.

Example 15: Errors during FETCH processing

If the FETCH statement fails while retrieving rows from the result table of the insert operation, a negative SQLCODE is returned to the application, but the result table still contains the original number of rows that was determined during the OPEN cursor processing. At this point, you can undo all of the inserts.

Assume that the result table contains 100 rows and the 90th row that is being fetched from the cursor returns a negative SQLCODE:

EXEC SQL DECLARE CS1 CURSOR FOR
  SELECT EMPNO
  FROM FINAL TABLE (INSERT INTO EMPSAMP (NAME, SALARY)
                    SELECT FIRSTNAME || MIDINIT || LASTNAME, SALARY + 10000.00 
                      FROM DSN8D10.EMP);
EXEC SQL OPEN CS1;                                 /* Inserts 100 rows */
while (SQLCODE == 0) 
  EXEC SQL FETCH CS1 INTO :hv_empno;
if (SQLCODE == -904)           /* If SQLCODE is -904, undo all inserts */
  EXEC SQL ROLLBACK;
else                                           /* Else, commit inserts */
  EXEC SQL COMMIT;