Snowflake – CREATE TABLE as SELECT

Snowflake SnowSQL provides CREATE TABLE as SELECT (also referred to as CTAS) statement to create a new table by copy or duplicate the existing table or based on the result of the SELECT query. If the table already existing, you can replace it by providing the REPLACE clause.

Duplicating a table would be very helpful when you wanted to take the backup of an existing table before applying any changes

Using this you can do the following. Note that all these create a new table or replace the existing 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 Snowflake CREATE TABLE as SELECT syntax. You can create a new table on a current schema or another schema.

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.

Copy or 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 of Snowflake, 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 Snowflake article, you have learned syntax for CREATE TABLE as SELECT and 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 LIKE
• How to Load JSON file into Snowflake table
• SnowSQL – Unload Snowflake table to Parquet file
• Snowflake SQL – SELECT INTO or similar
• SnowSQL – Unload Snowflake Table to CSV file
• SnowSQL – Unload Snowflake table to Amazon S3
• Load CSV file into Snowflake Database table

Reference

Create a table

Happy Learning !!