CREATE TABLE LIKE

Creates a new table with all of the same metadata of another table.

Grammar Syntax

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

Click here to go to the specification for this syntax.

Sample InputCode

CREATE TABLE `mytable`
LIKE mydataset.sourcetable

Snowflake OutputCode

CREATE TABLE `mytable`
LIKE mydataset.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_like`
LIKE `table1`
OPTIONS (
    labels=[("org_unit", "development")], 
    description="table like from table1", 
    expiration_timestamp=TIMESTAMP "2025-01-01 00:00:00 UTC"
);

Snowflake OutputCode

CREATE TABLE `table_like`
LIKE `table1`
COMMENT 'table like 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.

Related EWIs

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