Snowflake SQL provides “CREATE TABLE LIKE” statement to create a new table with just the structure of the existing table without copying the data.

CREATE TABLE LIKE copies the following from existing table.

  • Exactly same column names
  • Same column types
  • Default values And
  • constraints

Duplicating a table structure would be very helpful, one of the use case would be when you wanted to create a new schema with all the tables of an existing schema.


Here is the simplified version of the Snowflake CREATE TABLE LIKE syntax. You can create a new table on a current schema or another schema.

CREATE [ OR REPLACE ] TABLE &lttable_name&gt LIKE &ltsource_table&gt

Let’s assume you have a database “EMPLOYEE” and schema “PUBLIC” with table “EMP“. And the table has the following structure.

Snowflake create table select

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

snowflake select table

Copy or Duplicate table from an existing table

Below SQL query create EMP_COPY table with the same column names, column types, default values, and constraints from the existing table but it won’t copy the data.


You can execute the above command either from Snowflake web console interface or from SnowSQL and you get the same result.


In this Snowflake article, you have learned syntax for CREATE TABLE LIKE and how to create a new table by copy the structure of the existing table without copying the data.


Create a table

Happy Learning !!

Naveen (NNK)

Naveen (NNK) is a Data Engineer with 20+ years of experience in transforming data into actionable insights. Over the years, He has honed his expertise in designing, implementing, and maintaining data pipelines with frameworks like Apache Spark, PySpark, Pandas, R, Hive and Machine Learning. Naveen journey in the field of data engineering has been a continuous learning, innovation, and a strong commitment to data integrity. In this blog, he shares his experiences with the data as he come across. Follow Naveen @ @ LinkedIn

Leave a Reply

You are currently viewing Snowflake – CREATE TABLE LIKE