By using SQL data manipulation statement, a new, temporary, backup or regular table can be created and filled or inserted with data from another table or tables retrieved by SELECT statement. The SQL data manipulation language is CREATE TABLE AS which can be used to create a new table built from contents of result set by a query on a table that already exists within the database. Both the column types, and row data for the new table, come from the SELECT command specified by select.

CREATE TABLE AS has the following syntax or synopsis in simple form:

CREATE TABLE new_table_name [ ( column [, ...] ) ] AS SELECT [ ( column [, ...] ) ] FROM existing table_name

The SELECT statement can be in a complex form where data is been retrieved from several tables. For the new table, column names can be specified by including the column names in a comma-delimited list. Very important point to take note is that there should be the same number of columns specified in the column list preceding the AS clause (optional, if specified) for new table as the number of target columns that are returned by the select statement. If the optional list of columns in parentheses of new table contains different number of rows than the rows the SELECT statement returns, the following error message will be displayed:

ERROR: CREATE TABLE/AS SELECT has mismatched column count

In its simplest form, CREATE TABLE AS statement will create a complete backup of an existing table including all columns and data simply by using the statement:

CREATE TABLE new_table_name AS SELECT * FROM existing_table_name

You May Also Interested In: