Selecting values while inserting data

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);

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.

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.

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.

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

For this example, :empstruct is a host variable structure that is declared with variables for each of the columns in the EMPSAMP table.

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.

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.

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.

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

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

EXEC SQL DECLARE CS3 CURSOR FOR
  SELECT EMP_ROWID
  FROM FINAL TABLE (INSERT INTO DSN8A10.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.

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

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

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);

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.

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.

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.

EXEC SQL DECLARE CS2 CURSOR WITH HOLD FOR
  SELECT EMP_ROWID
  FROM FINAL TABLE (INSERT INTO DSN8A10.EMP_PHOTO_RESUME (EMPNO)
                    SELECT EMPNO FROM DSN8A10.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 */

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.

EXEC SQL DECLARE CS3 CURSOR FOR
  SELECT EMP_ROWID
  FROM FINAL TABLE (INSERT INTO DSN8A10.EMP_PHOTO_RESUME (EMPNO)
                    SELECT EMPNO FROM DSN8A10.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 DSN8A10.EMP_PHOTO_RESUME */ 
...
EXEC SQL ROLLBACK TO SAVEPOINT A;            /* All inserted rows are undone */

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

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

EXEC SQL DECLARE CS1 CURSOR FOR
  SELECT EMPNO
  FROM FINAL TABLE (INSERT INTO EMPSAMP (NAME, SALARY)
                    SELECT FIRSTNAME || MIDINIT || LASTNAME, SALARY + 10000.00 
                      FROM DSN8A10.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;