INSERT Statement Extension
Translation reference to convert Oracle INSERT Statement Extension to Snowflake Scripting
Description
The PL/SQL extension to the SQL
INSERT
statement lets you specify a record name in thevalues_clause
of thesingle_table_insert
instead of specifying a column list in theinsert_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
CREATE TABLE numbers_table(num integer, word varchar2(20));
INSERT Statement Extension simple case
Oracle
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 ;
Snowflake Scripting
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;
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.
Last updated