Create Snowflake stored procedure using multiple CTEs and insert the final CTE output into a table

user_11410401 0
Viewed 575

I have a situation where I have used multiple CTE statements and I want to insert my final CTE output into a table in a stored procedure - is this possible? If yes - how?

CREATE OR REPLACE PROCEDURE sp5()
RETURNS string
LANGUAGE javascript
AS
  $$

  var my_sql_command = 'with ct2 (emp_name,emp_id) as (select emp_name,emp_id  from TEST_1.PUBLIC.TEST11)
  insert into TEST_1.PUBLIC.EMP1 (emp_name,emp_id)  select emp_name,emp_id from ct2';
  
  var statement1 = snowflake.createStatement( {sqlText: my_sql_command} );

  var result_set1 = statement1.execute();


  return  "Done";
  $$;
  
 call sp5();

Error

JavaScript compilation error: Uncaught SyntaxError: Invalid or unexpected token in SP5 at ' var my_sql_command = 'with ct2 (emp_name, emp_id) as (select emp_name, emp_id from TEST_1.PUBLIC.TEST11)' position 23

snowflake-cloud-data-platform insert common-table-expression stored-procedures
2 Answers

Yes, it is possible to use INSERT INTO together with CTE.

Instead of:

with ct2 (emp_name,emp_id) as (
     select emp_name,emp_id from TEST_1.PUBLIC.TEST11
) 
insert into TEST_1.PUBLIC.EMP1 (emp_name,emp_id) 
select emp_name,emp_id from ct2

use:

insert into TEST_1.PUBLIC.EMP1 (emp_name,emp_id) 
with ct2 (emp_name,emp_id) as (
     select emp_name,emp_id from TEST_1.PUBLIC.TEST11
) 
select emp_name,emp_id from ct2
user_50708790

I believe your problem is that javascript isn't handling the variable correctly. It doesn't know what to do with the newline in your query string.

Try this. The difference is using a "backtick" (left of the 1 key) instead of a single quote to denote a javascript multi-line string.

CREATE OR REPLACE PROCEDURE sp5()
RETURNS string
LANGUAGE javascript
AS
  $$

  var my_sql_command = `with ct2 (emp_name,emp_id) as (select emp_name,emp_id  from TEST_1.PUBLIC.TEST11)
  insert into TEST_1.PUBLIC.EMP1 (emp_name,emp_id)  select emp_name,emp_id from ct2;`;
  
  var statement1 = snowflake.createStatement( {sqlText: my_sql_command} );

  var result_set1 = statement1.execute();


  return  "Done";
  $$;
  
 call sp5();
user_21178720
Related