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
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
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.
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.
Happy Learning !!