20 October 2018        Add to Favorites   Report

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;

Read great educational content like this and a lot more !

Members get free exclusive access to content, new courses, and discounts. Signup for a free account to write a post / comment / upvote posts. Creating an account takes less than 5 seconds and you can start earning badges & points too