SELECT INTO syntax for Snowflake Datawarehouse

Question

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.

Wow. That's a really documentation-free website they've got there, isn't it? — Matt Gibson, Nov 08 '15 at 21:38
@MattGibson There's documentation inside the tool. I think they are only recently beginning to open up to a larger customer base. — Kirk Broadhurst, Nov 09 '15 at 20:50

Mark Weaver · Answer 1 · 2016-02-19T23:25:53.637

29

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!

edited Feb 19 '16 at 23:25

answered Jan 28 '16 at 18:21

Mark Weaver

291
3
4

score 1 · Answer 2 · answered Apr 24 '20 at 16:58

1

CREATE TABLE summary AS SELECT Date, UserID, Value FROM raw_data

answered Apr 24 '20 at 16:58

Andrés Castrillo

61
1
5

score -4 · Answer 3 · answered Sep 10 '19 at 15:31

-4

You can also use the standard "WITH AS" nomenclature:

    with a as (
        SELECT
        Date
        ,UserID
        ,Value
        FROM
        raw_data)
    SELECT * FROM a

answered Sep 10 '19 at 15:31

Alex Cook

19
2

Will give you a CTE. – RagePwn May 20 '21 at 18:29
You are right...now I see why people are voting it down :) – Alex Cook May 21 '21 at 19:30

SELECT INTO syntax for Snowflake Datawarehouse

3 Answers3