SELECT INTO Statement

Translation reference to convert Oracle SELECT INTO statement to Snowflake Scripting

Some parts in the output code are omitted for clarity reasons.

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

IN -> Oracle_01.sql

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

OUT -> Oracle_01.sql

CREATE OR REPLACE TABLE numbers_table (num integer,
word VARCHAR(20))
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;

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

CREATE OR REPLACE TABLE aux_numbers_table (aux_num integer,
aux_word VARCHAR(20))
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;

SELECT INTO Statement simple case

Oracle

Query

IN -> Oracle_02.sql

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

OUT -> Oracle_02.sql

CREATE OR REPLACE PROCEDURE proc_select_into_variables ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
	DECLARE
		number_variable integer;
		word_variable VARCHAR(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     |

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

No related EWIs.