CREATE TABLE AS SELECT from another table in Snowflake (Copy DDL and Data)

Often, we need a safe backup of a table for comparison purposes or simply as a safe backup. To create a new table similar to another table copying both data and the structure,

create table mytable_copy as select * from mytable;

If you are using derivations in the select clause, make sure you have a valid alias, if you don't have a valid alias, then you might want to use this,

CREATE TABLE <table_name> ( <col1_name> , <col2_name> , ... ) AS SELECT ...

The number of column names specified must match the number of SELECT list items in the query; the types of the columns are inferred from the types produced by the query.

Clustering keys can be used in a CTAS statement; however, if clustering keys are specified, column definitions are required and must be explicitly specified in the statement.

Here's another advanced example of creating a table by selecting from an existing table; in this example, the values in the summary_amount column in the new table are derived from two columns in the source table 

create table employee-summary (name, summary_amount) 
as select name, amount1 + amount2 from source_table;

Atori

posted on 20 Oct 18