Selecting inserted values

You can retrieve the values for rows that are being inserted by specifying the INSERT statement in the FROM clause of a SELECT statement.

When you insert one or more rows into a table, you can select the result rows of the insert operation. These rows include any of the following values:

The value of any generated column, such as identity, ROWID, or row change timestamp columns
Any default values used for columns
All values for all rows inserted by a multiple-row insert operation
Values that were changed by a before insert trigger

The following example uses a table defined as follows:

  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 DEFAULT 'New Employee',
     HIREDATE  DATE NOT NULL WITH DEFAULT)

To insert a row for a new employee and see the values that were used for EMPNO, HIRETYPE, and HIREDATE, use the following statement:

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

The returned values are the generated value for EMPNO, 'New Employee' for HIRETYPE, and the current date for HIREDATE.