How to Create Temporary Table in Snowflake: Syntax and Example
In Snowflake, you create a temporary table using
CREATE TEMPORARY TABLE followed by the table name and columns. Temporary tables exist only during your session and are automatically dropped when the session ends.Syntax
The basic syntax to create a temporary table in Snowflake is:
CREATE TEMPORARY TABLE: Keyword to create a temporary table.table_name: Name you choose for your temporary table.(column_name data_type, ...): List of columns with their data types.
Temporary tables are session-scoped and automatically dropped at session end.
sql
CREATE TEMPORARY TABLE table_name ( column1 STRING, column2 INT );
Example
This example creates a temporary table named temp_employees with two columns: name and age. It then inserts two rows and selects all data from the table.
sql
CREATE TEMPORARY TABLE temp_employees ( name STRING, age INT ); INSERT INTO temp_employees (name, age) VALUES ('Alice', 30), ('Bob', 25); SELECT * FROM temp_employees;
Output
NAME | AGE
-------|----
Alice | 30
Bob | 25
Common Pitfalls
Common mistakes when creating temporary tables in Snowflake include:
- Forgetting that temporary tables are session-specific and disappear after the session ends.
- Trying to access a temporary table from a different session or user.
- Using
CREATE TABLEinstead ofCREATE TEMPORARY TABLEwhen a temporary table is needed.
Always ensure you create the table as temporary if you want it to be session-scoped.
sql
/* Wrong: Creates a permanent table instead of temporary */ CREATE TABLE temp_employees ( name STRING, age INT ); /* Right: Creates a temporary table */ CREATE TEMPORARY TABLE temp_employees ( name STRING, age INT );
Quick Reference
| Keyword | Description |
|---|---|
| CREATE TEMPORARY TABLE | Creates a temporary table that lasts only for the session |
| table_name | Name of the temporary table |
| column_name data_type | Defines columns and their data types |
| Session Scope | Temporary tables are visible only in the current session |
| Auto Drop | Temporary tables are dropped automatically when the session ends |
Key Takeaways
Use CREATE TEMPORARY TABLE to create session-scoped tables in Snowflake.
Temporary tables are automatically dropped at the end of your session.
Temporary tables cannot be accessed outside the session they were created in.
Always specify columns and data types when creating the table.
Avoid confusing temporary tables with permanent tables by using the correct keyword.
