Using Create Table...As Select
The CREATE TABLE...AS SELECT syntax creates a table from another table or tables. The new table is populated with the set of rows resulting from execution of the specified SELECT statement.
Note: The CREATE TABLE...AS SELECT syntax is an Ingres extension and not part of the ANSI/ISO Entry SQL-92 standard.
By default, the storage structure of the table is heap with compression. To override the default, issue the SET result_structure statement prior to issuing the CREATE TABLE...AS SELECT statement or specify the WITH STRUCTURE option.
By default, the columns of the new table have the same names as the corresponding columns of the base table from which you are selecting data. Different names can be specified for the new columns.
The data types of the new columns are the same as the data types of the source columns. The nullability of the new columns is determined as follows:
• If a source table column is nullable, the column in the new table is nullable.
• If a source table column is not nullable, the column in the new table is defined as not null.
If the source column has a default value defined, the column in the new table retains the default definition. However, if the default value in the source column is defined using an expression, the default value for the result column is unknown and its nullability depends on the source columns used in the expression. If all the source columns in the expression are not nullable, the result column is not nullable. If any of the source columns are nullable, the result column is nullable. Also see
Default Type Conversion (see page
Default Type Conversion).
A SYSTEM_MAINTAINED logical key column cannot be created using the CREATE TABLE...AS SELECT syntax. When creating a table using CREATE TABLE...AS SELECT, any logical key columns in the source table that are reproduced in the new table are assigned the format of NOT SYSTEM_MAINTAINED.