Snowflake SQL doesn’t have a “SELECT INTO” statement, however you can use “CREATE TABLE as SELECT” statement to create a table by copy or duplicate the existing table or based on the result of the SELECT query.
If you are coming from a traditional SQL background, you would be familiar with “SELECT INTO” statement which creates a new table and copies the data from the selected table to a new table,
Similarly, Snowflake has CREATE TABLE as SELECT (also referred to as CTAS) which creates a new table from the result of the SELECT query.
Using this you can do the following. note that all these create a table along with the data.
- Duplicate table from an existing table with all columns and rows
- Create a table with selected columns from the existing table
- Create a table by changing column names and data types of an existing table
- Create a table with the result of a select query.
CREATE TABLE as SELECT Syntax
Here is the simplified version of the CREATE TABLE as SELECT syntax.
CREATE [ OR REPLACE ] TABLE [dbname].[schema]. [ comma seperated columns with type]
AS SELECT [comma seperated columns] from [dbname].[schema].
[WHERE ]
-------
Let’s assume you have a database “EMPLOYEE” and schema “PUBLIC” with table “EMP“. And the table has the following structure. We use this table to explain with examples.
Snowflake SQL query SELECT * FROM EMPLOYEE.PUBLIC.EMP returns the contents of the EMP table.
Duplicate table from an existing table
Below SQL query create EMP_COPY table by duplicate the table structure and copying all the data from EMP table.
CREATE TABLE EMP_COPY as SELECT * FROM EMPLOYEE.PUBLIC.EMP
Create a table with selected columns from the existing table
In case if you wanted to create a new table with the selected columns, you can do this by supplying column names to select statement.
CREATE TABLE EMP_SEL_COL as SELECT FNAME,DEPARTMENT,SALARY FROM EMPLOYEE.PUBLIC.EMP
This creates a new table EMP_SEL_COL with columns FNAME,DEPARTMENT,SALARY and copies the data to these columns from EMP table.
Create a table by changing column names and data types
In this query, I would like to show how to create a table by changing column names and data types. With this, you need to be careful as changing data types some times loses data as well.
CREATE TABLE EMP_COL_CHANGE (FULL_NAME,DEPT STRING,SALARY) as SELECT FNAME,DEPARTMENT,SALARY FROM EMPLOYEE.PUBLIC.EMP
Here, we change FNAME to FULL_NAME and DEPARTMENT to DEPT along with datatype from float to varchar/string
Create a table with the result of a select query
Using CREATE TABLE as SELECT you can also run any qualified select statement and create the table with the result of the query.
CREATE TABLE EMP_COPY as SELECT * FROM EMPLOYEE.PUBLIC.EMP where DEPARTMENT=10
Conclusion
In this article, you have learned how to create a new table by copy or duplicate from an existing table or create a new table with the result of the select query.
Related Articles
- Snowflake – CREATE TABLE as SELECT
- Snowflake – CREATE TABLE LIKE
- How to Load JSON file into Snowflake table
- Load file from Amazon S3 into Snowflake table
- SnowSQL – Unload Snowflake Table to CSV file
- SnowSQL – Unload Snowflake table to Amazon S3
- SnowSQL – Unload Snowflake table to Parquet file
- Snowflake Create, Clone, Drop Database
