Creating a table using AS

 

You can create a table from the result of a SELECT statement. To create this type of table, use the CREATE TABLE AS statement.

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 all of the column definitions from EMPLOYEE where the DEPTNO = D11.

CREATE TABLE EMPLOYEE3 AS
   (SELECT PROJNO, PROJNAME, DEPTNO     FROM EMPLOYEE     WHERE DEPTNO = 'D11') WITH NO DATA

If the specified table or view contains an identity column, specify 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. The WITH NO DATA clause indicates that the column definitions are to be copied without the data. If you wanted 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.

 

Parent topic:

Data definition language

 

Related concepts

Retrieving data using the SELECT statement

 

Related reference

CREATE TABLE