CREATE TABLE AS

The CREATE TABLE AS commands creates a new table from the result of a select query.

Syntax

The following is the correct syntax for creating a table from the result of a select query:

create_table_statement ::=
    CREATE [ OR REPLACE ] TABLE [schema_name].table_name AS query
    ;

schema_name ::= identifier

table_name ::= identifier

Parameters

The following parameters can be used when creating a table from the result of a select query:

Parameter	Description
`OR REPLACE`	Create a new table, and overwrite any existing table by the same name. Does not return an error if the table already exists. `CREATE OR REPLACE` does not check the table contents or structure, only the table name.
`schema_name`	The name of the schema in which to create the table.
`table_name`	The name of the table to create, which must be unique inside the schema.
`query`	A select query that returns data

Permissions

The role must have the CREATE permission at the schema level, as well as SELECT permissions for any tables referenced by the statement.

Examples

This section includes the following examples:

Creating a Copy of a Foreign Table or View
Filtering
Adding Columns
Creating a Table From Values

Warning

The SELECT statement decrypts information by default. When executing CREATE TABLE AS SELECT, encrypted information will appear as clear text in the newly created table.

Creating a Copy of a Foreign Table or View 

CREATE TABLE users AS SELECT * FROM users_source;

For more information, see CREATE FOREIGN TABLE.

Filtering 

CREATE TABLE users_uk AS SELECT * FROM users WHERE country = 'United Kingdom';

Adding Columns 

CREATE TABLE users_uk_new AS SELECT GETDATE() as "Date",*,false as is_new FROM users_uk;

Creating a Table From Values 

CREATE TABLE new_users
  AS VALUES(GETDATE(),'Richard','Foxworthy','1984-03-03',True)