As SubQuery

Description

Specifies that the new table's columns have the same name, data type, and optionally the same data, as the result from the subquery.

As subquery create table is partially supported in Snowflake. The with or without data option is not applicable in Snowflake.

Click here to navigate to the Ansi SQL docs page for this syntax.

Grammar Syntax

 <as subquery clause> ::=
  [ <left paren>  <column name list>  <right paren>  ] AS <table subquery> 
      <with or without data> 

<with or without data> ::=
    WITH NO DATA
  | WITH DATA

Sample Source Patterns

ANSI SQL Input Code

With Data Option

CREATE TABLE TABLE01 AS
   SELECT
      *
   FROM
      TABLE02 WITH DATA

With No Data Option

CREATE TABLE TABLE01 AS 
   SELECT 
      * 
   FROM 
      TABLE02 WITH NO DATA;

Snowflake

With Data Option

CREATE TABLE PUBLIC.TABLE01 AS
   SELECT
      *
   FROM
      PUBLIC.TABLE02
-- ** MSC-WARNING - MSCEWI1002 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE. WITH OR WITHOUT DATA **
--                     WITH DATA

With No Data Option

CREATE TABLE PUBLIC.TABLE01 AS
   SELECT
      *
   FROM
      PUBLIC.TABLE02
   LIMIT 0
-- ** MSC-WARNING - MSCEWI1099 - CREATE TABLE AS SUBQUERY WITH NO DATA IS NOT SUPPORTED IN SNOWFLAKE, TRANSLATED TO LIMIT 0 **
-- WITH NO DATA

Related EWIs

1. MSCEWI1002 : NOT APPLICABLE IN SNOWFLAKE

2. MSCEWI1099 : CREATE TABLE AS SUBQUERY WITH NO DATA IS NOT SUPPORTED IN SNOWFLAKE, TRANSLATED TO LIMIT 0