Creating a Table by Using a Subquery Syntax
•Create a table and insert rows by combining the CREATE TABLE statement and the AS subquery option.
•Match the number of specified columns to the number of subquery columns.
Define columns with column names and default values.
Creating a Table from Rows in Another Table
A second method for creating a table is to apply the AS subquery clause, which both creates the table and inserts rows returned from the subquery.
In the syntax:
table is the name of the table
column is the name of the column, default value, and integrity constraint
subquery is the SELECT statement that defines the set of rows to be inserted into the new table
The table is created with the specified column names, and the rows retrieved by the SELECT statement are inserted into the table.
The column definition can contain only the column name and default value.
If column specifications are given, the number of columns must equal the number of columns in the subquery SELECT list.
If no column specifications are given, the column names of the table are the same as the column names in the subquery.
The integrity rules are not passed onto the new table, only the column data type definitions.