Execute SQL Statements from File in Snowflake — The Complete Guide

We can now execute the SQL statements specified in a file in a stage. The file should contain syntactically correct SQL statements or Snowflake Scripting blocks. This feature can be used as deployment mechanism to manage the objects and code in the projects that are not having CI/CD services.

1. Create SQL file such as below with name demo_load_script.sql which is to create a table mycsvtable and load the files from internal stage LOAD_FILES.

use database demo_db;

create or replace table mycsvtable (
  id integer,
  last_name string,
  first_name string,
  company string,
  email string,
  workphone string,
  cellphone string,
  streetaddress string,
  city string,
  postalcode string);


copy into mycsvtable from @load_files 
file_format = (type = csv skip_header = 1 field_delimiter = '|') 
on_error = continue;


select * from mycsvtable;

sample files in internal stage

2. Upload the SQL file in the internal stage SCRIPTS

SQL File

3. Run the SQL file using the command EXECUTE IMMEDIATE FROM to give the output of the last statement which is to select the records from the loaded table.

EXECUTE IMMEDIATE FROM @scripts/demo_load_script.sql;

4. Incase if the SQL file such as below with name demo_load_script_error.sql has an error, then EXECUTE IMMEDIATE FROM command fails and returns the error message of the failed statement. Any statements in the file prior to the failed statement wouldn’t be committed.

use database demo_db;

create or replace table mycsvtable_1 (
  id integer,
  last_name string,
  first_name string,
  company string,
  email string,
  workphone string,
  cellphone string,
  streetaddress string,
  city string,
  postalcode string);

copy into mycsvtable_1 from @load_files 
file_format = (type = csv skip_header = 1 field_delimter = '|') 
on_error = continue;
-- field_delimter word is wrong which has to be field_delimiter

select * from mycsvtable_1;

On running the command EXECUTE IMMEDIATE FROM the error message invalid parameter ‘field_delimter’ is shown in output results. On further lookout, the table mycsvtable_1 wouldn’t be created.

References:

https://docs.snowflake.com/en/sql-reference/sql/execute-immediate-from

Follow and Clap if you like the content and feel free to ask if you have any questions in the comments. I will be more than happy to assist and guide you.