Creating a table using AS

All of the expressions that can be used in a SELECT statement can be used in a CREATE TABLE AS statement. You can also include all of the data from the table or tables that you are selecting from.

For example, create a table named EMPLOYEE3 that includes a subset of column definitions and data from the EMPLOYEE table where the WORKDEPT = D11.

CREATE TABLE EMPLOYEE3 AS
   (SELECT EMPNO, LASTNAME, JOB
    FROM EMPLOYEE
    WHERE WORKDEPT = 'D11') WITH DATA

If the specified table or view contains an identity column, you must specify the option INCLUDING IDENTITY on the CREATE TABLE statement if you want the identity column to exist in the new table. The default behavior for CREATE TABLE is EXCLUDING IDENTITY. There are similar options to include the default value, the hidden attribute, and the row change timestamp attribute. The WITH NO DATA clause indicates that the column definitions are to be copied without the data. If you want to include the data in the new table EMPLOYEE3, include the WITH DATA clause. If the specified query includes a non-SQL-created physical file or logical file, any non-SQL result attributes are removed.