SELECT INTO Statement

Translation reference to convert Oracle SELECT INTO statement to Snowflake Scripting

Description

The SELECT INTO statement retrieves values from one or more database tables (as the SQL SELECT statement does) and stores them in variables (which the SQL SELECT statement does not do). (Oracle PL/SQL Language Reference SELECT INTO Statement)

Oracle SELECT INTO Syntax

SELECT [ { DISTINCT | UNIQUE } | ALL ] select_list
    { into_clause | bulk_collect_into_clause } FROM rest-of-statement ;

Oracle Into Clause Syntax

INTO { variable [, variable ]... | record )

Oracle Bulk Collect Syntax

BULK COLLECT INTO { collection | :host_array }
  [, { collection | :host_array } ]...

Snowflake Scripting SELECT INTO Syntax

SELECT [ { ALL | DISTINCT } ]
    {
          [{<object_name>|<alias>}.]*
        | [{<object_name>|<alias>}.]<col_name>
        | [{<object_name>|<alias>}.]$<col_position>
        | <expr>
        [ [ AS ] <col_alias> ]
    }
    [ , ... ]
    INTO :<variable> [, :<variable> ... ]
    [...]

Sample Source Patterns

Sample auxiliary data

This code was executed to a better understanding of the examples:

Oracle

CREATE TABLE numbers_table(num integer, word varchar2(20));
INSERT INTO numbers_table VALUES (1, 'one');
CREATE TABLE aux_numbers_table(aux_num integer, aux_word varchar2(20));

Snowflake

CREATE OR REPLACE TABLE PUBLIC.numbers_table (num integer,
word VARCHAR(20));

INSERT INTO PUBLIC.numbers_table VALUES (1, 'one');

CREATE OR REPLACE TABLE PUBLIC.aux_numbers_table (aux_num integer,
aux_word VARCHAR(20));

SELECT INTO Statement simple case

Oracle

Query

CREATE OR REPLACE PROCEDURE proc_select_into_variables
AS
number_variable integer;
word_variable varchar2(20);
BEGIN 
	SELECT * INTO number_variable, word_variable FROM numbers_table;
	INSERT INTO aux_numbers_table VALUES(number_variable, word_variable);	
END;

CALL proc_select_into_variables();
SELECT * FROM aux_numbers_table;

Result

|AUX_NUM|AUX_WORD|
|-------|--------|
|1      |one     |

Snowflake Scripting

Query

CREATE OR REPLACE PROCEDURE PUBLIC.proc_select_into_variables ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
   DECLARE
      number_variable integer;
      word_variable STRING;
   BEGIN
      SELECT * INTO :number_variable, :word_variable FROM PUBLIC.numbers_table;
      INSERT INTO PUBLIC.aux_numbers_table VALUES(:number_variable, :word_variable);
   END;
$$;

CALL PUBLIC.proc_select_into_variables();

SELECT * FROM PUBLIC.aux_numbers_table;

Result

|AUX_NUM|AUX_WORD|
|-------|--------|
|1      |one     |

Known Issues

1. BULK COLLECT INTO is not supported

Snowflake Scripting does not support the BULK COLLECT INTO clause. However, it is possible to use ARRAY_AGG to construct a new variable. For more information please see the Collection Bulk Operations Section.

2. Collections and records are not supported

Snowflake Scripting does not support the use of collections nor records. It is possible to migrate them using Semi-structured data types as explained in this section.

Related EWIs

1. MSCEWI1058: Functionality is not currently supported by Snowflake Scripting