Snowflake SQL – SELECT INTO or similar

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 create table select

Snowflake SQL query SELECT * FROM EMPLOYEE.PUBLIC.EMP returns the contents of the EMP table.

snowflake select 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.

snowflake duplicate 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

snowflake copy table

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.

Reference

Create a table

Tags: , , , , ,

Naveen (NNK)

I am Naveen (NNK) working as a Principal Engineer. I am a seasoned Apache Spark Engineer with a passion for harnessing the power of big data and distributed computing to drive innovation and deliver data-driven insights. I love to design, optimize, and managing Apache Spark-based solutions that transform raw data into actionable intelligence. I am also passion about sharing my knowledge in Apache Spark, Hive, PySpark, R etc.

Leave a Reply