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 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

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

CREATE OR REPLACE PROCEDURE PUBLIC.proc_insert_statement ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
   DECLARE
      number_variable integer := 10;
      word_variable STRING := 'ten';
   BEGIN
      INSERT INTO PUBLIC.numbers_table VALUES(:number_variable, :word_variable);
      INSERT INTO PUBLIC.numbers_table VALUES(11, 'eleven');
   END;
$$;

CALL PUBLIC.proc_insert_statement();

SELECT * FROM PUBLIC.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

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