INSERT Statement Extension

Translation reference to convert Oracle INSERT Statement Extension to Snowflake Scripting

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

Description

The PL/SQL extension to the SQL INSERT statement lets you specify a record name in the values_clause of the single_table_insert instead of specifying a column list in the insert_into_clause. (Oracle PL/SQL Language Reference INSERT Statement Extension)

Snowflake INSERT INTO differs from Snowflake Scripting in variable constraints; needing to have the names preceded by a colon ':' in order to bind the variables' value.

Recommendations

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

Oracle

CREATE TABLE numbers_table(num integer, word varchar2(20));

Snowflake

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

INSERT Statement Extension simple case

Oracle

Query

IN -> Oracle_01.sql

CREATE OR REPLACE PROCEDURE proc_insert_statement
AS
number_variable integer := 10;
word_variable varchar2(20) := 'ten';
BEGIN 
	INSERT INTO numbers_table VALUES(number_variable, word_variable);	
	INSERT INTO numbers_table VALUES(11, 'eleven');	
END;

CALL proc_insert_statement();
SELECT * FROM numbers_table ;

Result

|NUM|WORD  |
|---|------|
|10 |ten   |
|11 |eleven|

Snowflake Scripting

Query

OUT -> Oracle_01.sql

CREATE OR REPLACE PROCEDURE proc_insert_statement ()
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 := 10;
		word_variable VARCHAR(20) := 'ten';
	BEGIN
		INSERT INTO numbers_table
		VALUES(:number_variable, :word_variable);
		INSERT INTO numbers_table
		VALUES(11, 'eleven');
	END;
$$;

CALL proc_insert_statement();

SELECT * FROM
	numbers_table;

Result

|NUM|WORD  |
|---|------|
|10 |ten   |
|11 |eleven|

Known Issues

1. Records are not supported by Snowflake Scripting

Since records are not supported by snowflake scripting, instead of using the VALUES record clause, it is necessary to change it into a SELECT clause and split the columns of the record. For more information please see the Record Type Definition Section.

Related EWIs

No related EWIs.