Bird
Raised Fist0
SnowflakeHow-ToBeginner · 3 min read

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 named new_table with data and columns from the SELECT query.
  • You can also use CREATE OR REPLACE TABLE to 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 SELECT copies data immediately; it is not a view.
  • Not specifying OR REPLACE when 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

CommandDescription
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 copiedAdd 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.