CREATE TABLE CLONE

Creates a table clone based on a source table. The source table can be a table, a table clone, or a table snapshot.

Grammar Syntax

CREATE [ OR REPLACE ] TABLE [ IF NOT EXISTS ]
destination_table_name
CLONE source_table_name [FOR SYSTEM_TIME AS OF time_expression]
...
[OPTIONS(table_option_list)] 

Click here to go to the specification for this syntax.

Sample InputCode

CREATE TABLE mytable
CLONE anothertable FOR SYSTEM_TIME AS OF CURRENT_TIMESTAMP();

Snowflake OutputCode

CREATE TABLE mytable 
CLONE anothertable AT (TIMESTAMP => CURRENT_TIMESTAMP() /*** MSC-ERROR - MSCEWI1073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'CURRENT_TIMESTAMP' NODE ***/);

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_clone`
CLONE `table1`
OPTIONS(
  expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 365 DAY),
  friendly_name="my_clone_table",
  description="table cloned from table1",
  labels=[("org_unit", "development")]
);

Snowflake OutputCode

CREATE TABLE `table_clone` CLONE `table1`
COMMENT 'table cloned from table1'
----** MSC-ERROR - MSC-BQ0001 - THE OPTIONS CLAUSE WITHIN CREATE TABLE IS NOT SUPPORTED IN SNOWFLAKE WHEN IT CONTAINS THE FOLLOWING OPTIONS EXPIRATION_TIMESTAMP, FRIENDLY_NAME, LABELS. **
--OPTIONS(
--  expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 365 DAY),
--  friendly_name="my_clone_table",
--  labels=[("org_unit", "development")]
--)
 ;

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.

Related EWIs

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