Selecting inserted values

Edit online

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.