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.
<a id="h-create-table-like-syntax"></a>
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.
<a id="h-copy-or-duplicate-table-from-an-existing-table"></a>
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.
<a id="h-conclusion"></a>
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.
<a id="h-related-articles"></a>
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
<a id="h-reference"></a>
Reference
Happy Learning !!