how can I use one SQL script for multiple database/warehouse using Snowsql?

Question

I have define the variables inside the SQL script for database and schema. So is there any way to run that SQL script in different databases and schema at the same time?

my SQL script looks like this,

use database &{db};
use schema &{sc};

create table a_multi_test
(
id int
, name varchar
);

here what I run in powershell,

snowsql -c my_conn -f ...\multi_test.sql -D  db=dev -D  sc=schm  -D sc=schm2;

I was successfully able to create table but only in second(schm2) schema. It selected second schema not the both. Also, how can I define variable for warehouse as well? or I only can do that in config file by setting up connection parameters? Please help me through this and feel free to suggest any good documentation about Snowsql variables.

Thanks!

Answer 1

Why don't you run the script twice? If you specify the same variable name twice SnowSQL is just going to take the last one which is what you're seeing (schm2).

Here is how you can run them both at the same time with a shell script:

snowsql -c my_conn -f ...\multi_test.sql -D  db=dev -D sc=schm1& \
snowsql -c my_conn -f ...\multi_test.sql -D  db=dev -D sc=schm2&