CREATE TABLE COPY

Creates a table that has the same metadata and data as another table. The source table can be a table, a table clone, or a table snapshot.

Grammar Syntax

CREATE [ OR REPLACE ] TABLE [ IF NOT EXISTS ] table_name
COPY source_table_name
...
[OPTIONS(table_option_list)] 

Click here to go to the specification for this syntax.

Sample InputCode

CREATE TABLE newtable
COPY sourceTable;

Snowflake OutputCode

CREATE TABLE newtable CLONE sourceTable;

If the Create Table has options, these will be commented since they are not supported in Snowflake. Except for the "description" option.

Sample InputCode

CREATE TABLE `table_copy`
COPY `table1`
OPTIONS (
    labels=[("org_unit", "development")], 
    description="table copy from table1", 
    expiration_timestamp=TIMESTAMP "2025-01-01 00:00:00 UTC"
);

Snowflake OutputCode

CREATE TABLE `table_copy` CLONE `table1`
COMMENT 'table copy from table1'
----** MSC-ERROR - MSC-BQ0001 - THE OPTIONS CLAUSE WITHIN CREATE TABLE IS NOT SUPPORTED IN SNOWFLAKE WHEN IT CONTAINS THE FOLLOWING OPTIONS LABELS, EXPIRATION_TIMESTAMP. **
--OPTIONS (
--    labels=[("org_unit", "development")],
--  expiration_timestamp=TIMESTAMP "2025-01-01 00:00:00 UTC"
--)
 ;

Note

• The LABELS are not transformed into TAGs because the TAGs of the source table are copied, they cannot be changed during the copy of the table.

• CREATE TABLE COPY and CREATE TABLE CLONE are functionally equivalent. This is why COPY is transformed into CLONE.

Related EWIs

1. MSC-BQ0001: The OPTIONS clause within CREATE TABLE is not supported in Snowflake when it contains the following options.