CREATE TABLE AS SELECT
Create a table from an existing table by copying the existing table's columns. The new table is populated with the records from the existing table.
Creates a TEMPORARY table as a copy of an existing table.
Syntax
CREATE [TEMPORARY] TABLE table-name AS QUERY_EXPRESSION
CREATE [TEMPORARY] TABLE new_table
AS (SELECT * FROM old_table);
Remarks
• The QUERY_EXPRESSION can be any select query without an ORDER BY or LIMIT clause.
• The temporary table will be empty on first access, can optionally be returned to empty state at every COMMIT by using the ON COMMIT clause. The temporary tables are automatically cleaned up by the server at the end of the user session. You can also explicitly drop them if needed in between the session.
• If most of the queries are going against a particular database, the performance of the joins on temporary table with the persisted table might be better with a specific temporary table storage location. The privileges associated with the Temporary Table Container affect the user who can create and use temporary tables if the DDL Container is set. The temporary table storage location can be changed by editing the Temporary Table Container configuration parameter through Studio.
Examples
CREATE TABLE queenbee
AS (SELECT * FROM babybee);
OR
CREATE TEMPORARY TABLE queenbee
AS (SELECT * FROM babybee);