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

Reference

Create a table