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