SELECT INTO

Description

SELECT INTO creates a new table and fills it with data computed by a query. The data is not returned to the client, as it is with a normal SELECT. The new table's columns have the names and data types associated with the output columns of the SELECT.

Click here to navigate to the PostgreSQL documentation page for this syntax.

The SELECT INTO is translated to CREATE OR REPLACE TABLE AS in snowflake.

Grammar Syntax

[ WITH [ RECURSIVE ] with_query [, ...] ]
SELECT [ALL | DISTINCT [ON ( expression [, ...] )]]
    * | expression [AS output_name] [, ...]
    INTO [TEMPORARY | TEMP | UNLOGGED ] [TABLE] new_table
    [FROM from_item [, ...]]
    [WHERE condition]
    [GROUP BY expression [, ...]]
    [HAVING condition [, ...]]
    [{UNION | INTERSECT | EXCEPT} [ALL | DISTINCT ] select]
    [ORDER BY expression [ASC | DESC | USING operator] [NULLS {FIRST | LAST}] [, ...]]
    [LIMIT {count | ALL}]
    [OFFSET start [ ROW | ROWS ] ]
    [FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]
    [FOR {UPDATE | SHARE} [OF table_name [, ...]] [NOWAIT] 
    [...]]

Sample Source Patterns

PostgreSQL

SELECT Col1, Col2 INTO NewTable FROM OldTable;

Snowflake

CREATE OR REPLACE TABLE NewTable AS
SELECT Col1, Col2
-- ** MSC-WARNING - MSC-PG0017 - SELECT WITH INTO CLAUSE IS NOT COMPATIBLE WITH SNOWFLAKE, IT IS TRANSFORMED TO A CREATE TABLE AS. **
--                  INTO NewTable
                                FROM
OldTable;

Related EWIs

1. MSC-PG0016: The lock level is handled automatically in Snowflake.

2. MSC-PG0017: Select with Into clause is not compatible with snowflake, it is transformed to a Create Table As.

3. MSC-PG0018: Select with Into clause is not compatible with snowflake, it is transformed to a Create Table As And unlogged is not compatible with snowflake, all transactions are logged.