Sql – SELECT INTO syntax for Snowflake Datawarehouse

insert-intosnowflake-cloud-data-platformsql

I believe there's an SELECT INTO-like syntax in Snowflake, but I am unable to find documentation or examples to use it.

CREATE TABLE raw_data (
    Timestamp TIMESTAMP NOT NULL, 
    Date DATE NOT NULL, 
    UserID STRING,
    Address STRING,
    Phone STRING,
    Value INTEGER
);

COPY INTO raw_data from 's3://my_bucket'
CREDENTIALS=(AWS_KEY_ID='XXXXX' AWS_SECRET_KEY='XXXX')
ON_ERROR=CONTINUE;

CREATE TABLE summary (
    Date DATE NOT NULL,
    UserID STRING,
    Value INTEGER
);

INSERT INTO summary 
SELECT Date, UserID, Value FROM raw_data

The above works, but rather than defining the table summary I want to SELECT INTO and have the SQL parser create the table for me.

Best Answer

You can use Create Table AS (CTAS) to accomplish this. In your case it would be:

CREATE TABLE SUMMARY AS
SELECT
  Date
, UserID
, Value
FROM
  raw_data;

Here is an example using no data that I have tested:

create table foo as select $1, $2
from

    values ( 1, 'two' ), ( 3, 'four' ), ( 5, 'six' );

    select * from foo;

Hope this helps!

Related Topic