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.
CREATE TABLE LIKE Syntax
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 <table_name> LIKE <source_table>
Let’s assume you have a database “EMPLOYEE
” and schema “PUBLIC
” with table “EMP
“. And the table has the following structure.

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 with the same column names, column types, default values, and constraints from the existing table but it won’t copy the data.
CREATE TABLE EMP_COPY LIKE EMPLOYEE.PUBLIC.EMP
You can execute the above command either from Snowflake web console interface or from SnowSQL and you get the same result.
Conclusion
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.
Related Articles
- Snowflake Create, Clone, Drop Database
- Snowflake – CREATE TABLE as SELECT
- Java- Create Snowflake table programmatically
- Scala – Create Snowflake table programmatically
- How to Load JSON file into Snowflake table
- Load file from Amazon S3 into Snowflake table
- How to Load Parquet file into Snowflake table
Reference
Happy Learning !!