SELECT INTO Statement

Translation reference to convert Oracle SELECT INTO statement to Snowflake Scripting

Important Notice: Migration of Documentation Website

Please be advised that our documentation website is currently undergoing a migration to a new platform. To ensure you have access to the most up-to-date information, we kindly request that you visit our new documentation website located at:

Official Snowflake Snowconvert Documentation

For any immediate assistance or if you encounter any issues, please contact our support team at [email protected].

Thank you for your understanding.

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

Oracle Bulk Collect Syntax

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

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

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

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.