Snowflake – CREATE TABLE LIKE

  • Post author:
  • Post category:Snowflake
  • Post last modified:October 10, 2020

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


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.

Reference

Create a table

Happy Learning !!

NNK

SparkByExamples.com is a Big Data and Spark examples community page, all examples are simple and easy to understand and well tested in our development environment Read more ..

Leave a Reply

You are currently viewing Snowflake – CREATE TABLE LIKE