title: Snowflake Stored procedure Copy into Temporary table
body: <p>Question is related to Snowflake and Snowsql. That said I'm trying to within a stored proc create a temp table and then 'copy into' this temp table from azure blob storage.
  I'm manually executed the snow sql statements and they work fine.
  Statement 1:<code>CREATE TEMPORARY TABLE DB.TABLES.LINE_DETAILS_INCREMENTAL LIKE DB.TABLES.LINE_DETAILS;</code></p>
  <p>Statement 2:</p>
  <pre><code>COPY INTO DB.TABLES.LINE_DETAILS_INCREMENTAL FROM (SELECT * FROM @DB.BASE.Azure/S/LINE_DETAILS_INCREMENTAL )
  force = false file_format = (type = csv field_delimiter = '|' encoding = 'Windows 1252' skip_header = 0);
  </code></pre>
  <p>But when I encapsulate this into a stored proc and try to run it it gives error:-
  &quot;JavaScript compilation error: Uncaught SyntaxError: Unexpected identifier in SP_DELETE_LINE_DETAILS at ' var insert_clause = 'COPY INTO DB.TABLES.LINE_DETAILS_INCREMENTAL FROM (SELECT * FROM @Feeds_DB.BASE.Azure/S/LINE_DETAILS_INCREMENTAL ) force = true file_format = (type = csv field_delimiter = '|' encoding = 'Windows1252' skip_header = 0) On_error = continue;'' position 288 &quot;.
  Code of the stored proc is:-</p>
  <pre><code>CREATE or replace procedure  &quot;DB&quot;.&quot;TABLES&quot;.&quot;SP_DELETE_INSERT_3DAYS_INTO_LINE_DETAILS&quot;()
  returns varchar(1000)
  language javascript
  as
  $$
  try{
  var create_clause = 'CREATE TEMPORARY TABLE DB.TABLES.LINE_DETAILS_INCREMENTAL LIKE DB.TABLES.PS_TRANSACTION_LINE_DETAILS;'
          var create_stmt  = snowflake.createStatement({sqlText: create_clause});   
          var create_res = create_stmt.execute();
          
          var insert_clause = 'COPY INTO DB.TABLES.LINE_DETAILS_INCREMENTAL FROM (SELECT * FROM @Tableau_Feeds_DB.BASE_TABLES.JDAStagingAzure/POS/PS_TRANSACTION_LINE_DETAILS_INCREMENTAL ) force = true file_format = (type = csv field_delimiter = '|' encoding = 'Windows1252' skip_header = 0) On_error = continue;'
          var insert_stmt = snowflake.createStatement({sqlText: insert_clause});  
          var insert_res = insert_stmt.execute();
          var select_clause = 'select distinct TO_CHAR(TO_DATE(CREATE_DATE)) as CREATE_DATE from DB.TABLES.LINE_DETAILS_INCREMENTAL order by CREATE_DATE';
          var select_stmt  = snowflake.createStatement({sqlText: select_clause});
          var select_res   = select_stmt.execute();
   while (select_res.next()) 
              {
              date_ip = select_res.getColumnValue(1);
              var desc_user_sql  = `delete from DB.TABLES.LINE_DETAILS where TO_DATE(CREATE_DATE) = :1;`
              var desc_user_stmt  = snowflake.createStatement({sqlText: desc_user_sql, binds: [date_ip]});
  
              var desc_user_sql2  = `INSERT INTO DB.TABLES.PS_TRANSACTION_LINE_DETAILS select * from DB.TABLES.PS_TRANSACTION_LINE_DETAILS_INCREMENTAL where TO_DATE(CREATE_DATE) = :1;`
              var desc_user_stmt2  = snowflake.createStatement({sqlText: desc_user_sql2, binds: [date_ip]});
  
              try{
                         desc_user_stmt.execute();
                         desc_user_stmt2.execute();
                    }
                 catch(err)
                    {
                     return &quot;Error inserting records: &quot; +err;
                    }              
              }
              return &quot;Data has been insert in success!&quot;;
          }
          
          catch(err){
                 return &quot;Error whileselecting Roles : &quot; +err;
          }
  return 0;
  $$
  </code></pre>