How to Create Table from SELECT in Snowflake
In Snowflake, you can create a new table from a SELECT query using
CREATE TABLE new_table AS SELECT .... This copies the selected data and schema into the new table in one step.Syntax
The basic syntax to create a table from a SELECT query in Snowflake is:
CREATE TABLE new_table AS SELECT ...: Creates a new table namednew_tablewith data and columns from the SELECT query.- You can also use
CREATE OR REPLACE TABLEto overwrite an existing table.
sql
CREATE TABLE new_table AS SELECT column1, column2 FROM existing_table WHERE condition;
Example
This example creates a new table sales_summary from the sales table, selecting only sales from 2023.
sql
CREATE TABLE sales_summary AS SELECT product_id, SUM(amount) AS total_amount FROM sales WHERE sale_date >= '2023-01-01' GROUP BY product_id;
Output
Table SALES_SUMMARY created successfully with aggregated sales data for 2023.
Common Pitfalls
Common mistakes when creating tables from SELECT in Snowflake include:
- Forgetting that
CREATE TABLE AS SELECTcopies data immediately; it is not a view. - Not specifying
OR REPLACEwhen you want to overwrite an existing table, causing errors. - Assuming constraints or indexes are copied; Snowflake does not copy constraints with CTAS.
sql
/* Wrong: Trying to create a table that already exists without OR REPLACE */ CREATE TABLE sales_summary AS SELECT * FROM sales; /* Right: Use OR REPLACE to overwrite */ CREATE OR REPLACE TABLE sales_summary AS SELECT * FROM sales;
Quick Reference
| Command | Description |
|---|---|
| CREATE TABLE new_table AS SELECT ... | Create new table with data from SELECT query |
| CREATE OR REPLACE TABLE new_table AS SELECT ... | Create or overwrite table with SELECT data |
| SELECT * FROM new_table; | Query the newly created table |
| Note: Constraints and indexes are NOT copied | Add constraints manually if needed |
Key Takeaways
Use CREATE TABLE AS SELECT to create a new table with data from a query in one step.
Use CREATE OR REPLACE TABLE to overwrite an existing table without errors.
CTAS copies data and columns but does not copy constraints or indexes.
Always verify the new table's data after creation with a SELECT query.
Be careful with table names to avoid accidental overwrites.
