Snowflake – CREATE TABLE LIKE

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

Create a table

Happy Learning !!