Multiple merge statements in a snowflake transaction-CodePudding

I'm new to snowflake and just tried creating a stored procedure to read deltas from a stream table A and execute two merge statements into tables B & C, but I keep getting this error message "Multiple SQL statements in a single API call are not supported; use one API call per statement instead."

Here's my stored procedure. The idea is to call it from a task when system$stream_has_data('tableA')

create or replace procedure write_delta()
return varchar    
language sql as 
$$
    begin transaction;

    -- 1/2. load table B
    merge into tableB as dest
    using tableA as src 
        on src.id = dest.id
    when not matched
        then insert (id, value) values( src.id, src.value);
        
    -- 2/2. load tableC    
    merge into tableC as dest
    using tableA as src 
        on src.id = dest.id
    when not matched
        then insert (id, value) values( src.id, src.value); 
    commit;
$$;

CodePudding user response：

Snowflake SQL stored procedures take two forms.

Form 1: A single SQL statement. This is mostly useful when calling from a task to execute a single statment.

Form 2: A Snowflake Scripting block. The following script shows your code converted to a stored procedure with a scripting block:

create or replace table table1 (id int, value string);
create or replace table tableB like table1;
create or replace table tableC like table1;
create or replace stream tableA on table table1;

insert into table1(id, value) values (1, 'A'), (2, 'B');
    
create or replace procedure myprocedure()
  returns varchar
  language sql
  as
  $$
    begin
    begin transaction;

    -- 1/2. load table B
    merge into tableB as dest
    using tableA as src 
        on src.id = dest.id
    when not matched
        then insert (id, value) values( src.id, src.value);
        
    -- 2/2. load tableC    
    merge into tableC as dest
    using tableA as src 
        on src.id = dest.id
    when not matched
        then insert (id, value) values( src.id, src.value); 
    commit;
    end;
  $$
  ;

call myprocedure();

select * from tableB;
select * from tableC;

You can get more information on how to write them here: https://docs.snowflake.com/en/developer-guide/snowflake-scripting/index.html

If you want to execute multiple statements, you'll need to run the stored procedure using Snowflake Scripting, JavaScript, Java, or Python.