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;