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
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].<tablename> [ comma seperated columns with type] AS SELECT [comma seperated columns] from [dbname].[schema].<tablename> [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
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
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
SALARY and copies the data to these columns from
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
DEPT along with datatype from float to varchar/string
Create a table with the result of a select query
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
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.
Happy Learning !!