How to execute multiple statements in in a single API call?

[MahesRam]

My SQL has multiple SQL statements. While using execute reader method I am getting error {"Multiple SQL statements in a single API call are not supported; use one API call per statement instead."}

[howryu]

It is not supported by Snowflake right now (a.k.a backend did not support it either) and no other driver support multiple statements. However, this is in our roadmap.

[samrogers226]

@howryu any update on this?

[larfre]

@howryu Our company is also interested in this being updated.

[relloyd]

+1 from me.

[howryu]

Update: server side support of multi statement is in progress and JDBC will be the first driver supporting it. Then will come to .NET driver.

@hkapre is our PM and he will have a better timeline or ETA

[smtakeda]

For new feature requests, please reach out the support. We can discuss here but cannot commit it. Thank you.

[dinesh-procore]

@howryu Our company is interested in this as well.

[michaeldunnegan]

If you're interested in making multiple insert statements, this may help
https://docs.snowflake.net/manuals/sql-reference/sql/insert-multi-table.html

[WATYF]

Update: server side support of multi statement is in progress and JDBC will be the first driver supporting it. Then will come to .NET driver.

@hkapre is our PM and he will have a better timeline or ETA

@howryu Is there any update on this. We need to run queries with variables using the .NET driver.

[michael135]

We are also interested in multiple SQL statements via python api.
For me in such case I can provide few short queries instead of providing a long one.
Which is much more convenient to use and also to maintain.

[bplaats]

+1 here too, especially in the native stored procedure api, I could really use it now.

For python the concurrent futures package is helpful, I used it once

[benryan2010]

+1 here as well - I'm trying to write a Django backend for snowflake and multiple queries would be a really nice feature since snowflake has decided to omit the RETURNING (ansi standard command) from the dialect. I have to make three separate calls to get the last inserted id when one would due (I'm using the python API).

[RichieRunner]

any progress update after 3 years? 😔

[ghost]

@howryu is there any update on ODBC driver multiple query support. I am trying to run multiple queries in excel using ODBC snowflake driver connection.

[pranavq212]

Any updates on this, for .net core 3.* code works on local debugging & updates it. But on after deploying to AWS lamda it gives error :

"Message": "Multiple SQL statements in a single API call are not supported; use one API call per statement instead. , StackTrace: at Snowflake.Data.Core.SFStatement.BuildResultSet(QueryExecResponse response, CancellationToken cancellationToken)\n at Snowflake.Data.Core.SFStatement.Execute(Int32 timeout, String sql, Dictionary``2 bindings, Boolean describeOnly)\n at Snowflake.Data.Client.SnowflakeDbCommand.ExecuteInternal(Boolean describeOnly)\n at Snowflake.Data.Client.SnowflakeDbCommand.ExecuteNonQuery()"

[amine-mouffok]

Have you guys tried https://docs.snowflake.com/en/user-guide/python-connector-api.html#execute_string ?

[lukaszkoziara]

@amine-mouffok Yes, in our project we actively use execute_string, but that method has one disadvantage - lack of possibility to safely pass parameters to query.

[TiagoACS]

Hi,
Is there any update on this?
Execution of multiple SQL statements is supported by ODBC and JDBC drivers as well as in Snowflake SQL API. It would be very useful to have this feature in .Net driver too.

/Tiago Silva

[hans2520]

+1

[trofimchuk-t]

I've found a workaround which is works for me. You just need to wrap multiple statements with the BEGIN and END commands:

BEGIN
    <statement_1>;
    <statement_2>;
END;

With these operators I was able to use two MERGE commands to upsert parent/child data in one request.

[WRJE]

I've found a workaround which is works for me. You just need to wrap multiple statements with the BEGIN and END commands:

BEGIN
    <statement_1>;
    <statement_2>;
END;

With these operators I was able to use two MERGE commands to upsert parent/child data in one request.

You got my hopes up for a minute there. That may work with statements, but not with variables, which is what I need.

Any reason why this still hasn't been implemented almost four years later?

[TiagoACS]

Can you please provide an example @WRJE ?

[WRJE]

Can you please provide an example @WRJE ?

Any use of a var in a query, which I can do in the Snowflake console but cannot do via the .NET driver.

This example is from Snowflake's documentation:

set (min, max)=(40, 70);
select $min;

It runs fine in the console, but via the .NET driver it produces the "Multiple SQL Statements not supported" error. If I use BEGIN/END, it says "$MIN does not exist".

[TiagoACS]

Can you please provide an example @WRJE ?

Any use of a var in a query, which I can do in the Snowflake console but cannot do via the .NET driver.

This example is from Snowflake's documentation:

set (min, max)=(40, 70); select $min;

It runs fine in the console, but via the .NET driver it produces the "Multiple SQL Statements not supported" error. If I use BEGIN/END, it says "$MIN does not exist".

That's because you are using session variables. It would work with "regular" variables:
begin
let min := 40;
select :min;
--return :min;
end;
https://docs.snowflake.com/en/developer-guide/snowflake-scripting/variables.html#declaring-a-variable

Or if you set the session variable before the scripting block:
set (min, max)=(40, 70);
begin
set min = 10;
select $min; -- value 10
end;

[WRJE]

That's because you are using session variables. It would work with "regular" variables: begin let min := 40; select :min; --return :min; end; https://docs.snowflake.com/en/developer-guide/snowflake-scripting/variables.html#declaring-a-variable

Or if you set the session variable before the scripting block: set (min, max)=(40, 70); begin set min = 10; select $min; -- value 10 end;

OK, using the link you provided I was able to work up a sample of querying a value to use as a variable (which is what I needed this for):

BEGIN
LET cust VARCHAR := (SELECT TOP 1 Customer FROM Customers ORDER BY Sales DESC);
RETURN cust;
END;

So it looks like switching to "regular" variables (not sure the difference there either) allows me to at least setup a simple var and RETURN it. The problem is that I need these vars to be used in other SELECT statements. For example:

BEGIN
LET cust VARCHAR := (SELECT TOP 1 Customer FROM Customers ORDER BY Sales DESC);
SELECT * FROM Orders WHERE Customer = :cust;
END;

This runs without error in the .NET driver, but returns an empty anonymous block. No data. Even something as simple as 'SELECT :cust;" does the same. Can you use these vars in queries, or is that only for session variables?

[TiagoACS]

Yes, you can.

begin
let min := 40;
create or replace table MY_TABLE(MY_COL number) as select :min;
end;

SELECT * FROM MY_TABLE;

[WRJE]

Yes, you can.

begin let min := 40; create or replace table MY_TABLE(MY_COL number) as select :min; end;

SELECT * FROM MY_TABLE;

That isn't working for me. As-is, it produces the "multiple API calls" error because it sees the begin/end and SELECT as separate calls. If I put the SELECT inside the begin/end, it produces the same empty anonymous block, presumably because the SELECT in the CREATE statement is generating the same output as a stand-alone SELECT statement (which is an empty block).

[bcikili]

our company is also having issues and interested in any computation-wise solutions for this! hope to see some progress

[salvadorplj]

Hard to believe that this functionality doesn't exist at this point. A very basic capability.

[brettpostin]

I'm unable to get the BEGIN/END workaround to work with parameters.

The following results in a "SQL compilation error: error line 4 at position 42 invalid identifier 'GROUPCODE'":

var sql = $@"
BEGIN
    USE DATABASE {targetDatabase}; 
    CALL PIPELINES.ADD_CLIENTS_TO_ONBOARD(:groupCode, :clientCode, ',');
END";

Removing the BEGIN/END results in the original "Multiple SQL statements in a single API call are not supported; use one API call per statement instead." error:

var sql = $@"
    USE DATABASE {targetDatabase}; 
    CALL PIPELINES.ADD_CLIENTS_TO_ONBOARD(:groupCode, :clientCode, ',');
";

This line on it's own works fine:

var sql = $@"
    CALL PIPELINES.ADD_CLIENTS_TO_ONBOARD(:groupCode, :clientCode, ',');
";

Any ideas or suggestions?

[sfc-gh-hchaturvedi]

Multi Statement support for .NET has been addressed in the .NET release in November 2022 - For more details please refer to .NET release notes - https://community.snowflake.com/s/article/Dot-NET-Driver-Release-Notes

[sfc-gh-ext-simba-dl]

Add parameter "MULTI_STATEMENT_COUNT" to 3, which means the query contains 3 statements. set to 0 is no limit (but not recommended, because sql injection).

[dss010101]

does this work for python as well? seeing same frustrating issue with multi-statement queries and using set varaibles

[dss010101]

were you able to figure this out..the SELECT part?

[paulverbeke]

multi statement (batch) support has been added but only without bind parameters ?