EXECUTE IMMEDIATE FROM

EXECUTE IMMEDIATE FROM executes the SQL statements specified in a file in a stage. The file can contain SQL statements or Snowflake Scripting blocks. The statements must be syntactically correct SQL statements.

You can use the EXECUTE IMMEDIATE FROM command to execute the statements in a file from any Snowflake session.

This feature provides a mechanism to control the deployment and management of your Snowflake objects and code. For example, you can execute a stored script to create a standard Snowflake environment for all your accounts. The configuration script might include statements that create users, roles, databases, and schemas for every new account.

See also:

EXECUTE IMMEDIATE

Syntax

EXECUTE IMMEDIATE
  FROM { absoluteFilePath | relativeFilePath }
Copy

Where:

absoluteFilePath ::=
   @[ <namespace>. ]<stage_name>/<path>/<filename>
Copy
relativeFilePath ::=
  '[ { ./ | / | { ../ [ , ../, ... ] } } ]<path>/<filename>'
Copy

Absolute file path (absoluteFilePath)

namespace

Database and/or schema in which the internal or external stage resides, in the form of database_name.schema_name or schema_name. The namespace is optional if a database and schema are currently in use for the user session; otherwise, it is required.

stage_name

Name of the internal or external stage.

path

Case-sensitive path to the file in the stage.

filename

Name of the file to execute. It must contain syntactically correct and valid SQL statements. Each statement must be separated by a semicolon.

Relative file path (relativeFilePath)

path

Case-sensitive relative path to the file in the stage. Relative paths support established conventions such as a leading / to indicate the root of a stage’s file system, ./ to refer to the current directory (the directory the parent file is located in) and ../ to refer to the parent directory. For more information, see Usage Notes.

filename

Name of the file to execute. It must contain syntactically correct and valid SQL statements. Each statement must be separated by a semicolon.

Returns

EXECUTE IMMEDIATE FROM returns:

  • The result of the last statement in the file if all statements are successfully executed.

  • The error message, if any statement in the file failed.

    If there is an error in any statement in the file, the EXECUTE IMMEDIATE FROM command fails and returns the error message of the failed statement.

    Note

    If the EXECUTE IMMEDIATE FROM command fails and returns an error message, any statements in the file prior to the failed statement have successfully completed.

Access Control Requirements

  • The role used to execute the EXECUTE IMMEDIATE FROM command must have the USAGE (external stage) or READ (internal stage) privilege on the stage where the file is located.

  • The role used to execute the file can only execute the statements in the file for which it has privileges. For example, if there is a CREATE TABLE statement in the file, the role must have the necessary privileges to create a table in the account or the statement fails.

Note that operating on any object in a schema also requires the USAGE privilege on the parent database and schema.

For instructions on creating a custom role with a specified set of privileges, see Creating custom roles.

For general information about roles and privilege grants for performing SQL actions on securable objects, see Overview of Access Control.

Usage Notes

  • The SQL statements in a file to be executed can include EXECUTE IMMEDIATE FROM statements:

    • Nested EXECUTE IMMEDIATE FROM statements can use relative file paths.

      Relative paths are evaluated in respect to the stage and file path of the parent file. If the relative file path starts with /, the path starts at the root directory of the stage containing the parent file.

      For an example, see Examples.

    • Relative file paths must be enclosed in single quotes (') or $$.

    • The maximum execution depth for nested files is 5.

  • Absolute file paths can optionally be enclosed in single quotes (') or $$.

  • The file to be executed cannot be larger than 10MB in size.

  • The file to be executed must be encoded in UTF-8.

  • The file to be executed must be uncompressed. If you use the PUT command to upload a file to an internal stage, you must explicitly set the AUTO_COMPRESS parameter to FALSE.

    For example, upload my_file.sql to my_stage:

    PUT file://~/sql/scripts/my_file.sql @my_stage/scripts/
      AUTO_COMPRESS=FALSE;
    
    Copy
  • The execution of all files in a directory is not supported. For example, EXECUTE IMMEDIATE FROM @stage_name/scripts/ results in an error.

Troubleshooting EXECUTE IMMEDIATE FROM Errors

This section contains some common errors that result from an EXECUTE IMMEDIATE FROM statement and how you can resolve them.

File Errors

Error

001501 (02000): File '<directory_name>' not found in stage '<stage_name>'.

Cause

There are multiple causes for this error:

  • The file does not exist.

  • The file name is the root of a directory. For example @stage_name/scripts/.

Solution

Verify the name of the file and confirm the file exists. Executing all the files in a directory is not supported.

Error

001503 (42601): Relative file references like '<filename.sql>' cannot be used in top-level EXECUTE IMMEDIATE calls.

Cause

The statement was executed using a relative file path outside of a file execution.

Solution

A relative file path can only be used in EXECUTE IMMEDIATE FROM statements in a file. Use the absolute file path for the file. For more information, see Usage Notes.

Error

001003 (42000): SQL compilation error: syntax error line <n> at position <m> unexpected '<string>'.

Cause

The file contains SQL syntax errors.

Solution

Fix the syntax errors in the file and reupload the file to the stage.

Stage Errors

Error

002003 (02000): SQL compilation error: Stage '<stage_name>' does not exist or not authorized.

Cause

The stage does not exist or you do not have access to the stage.

Solution

  • Verify the name of the stage and confirm the stage exists.

  • Execute the statement using a role that has the required privileges to access the stage. For more information, see Access Control Requirements.

Access Control Errors

Error

003001 (42501): Uncaught exception of type 'STATEMENT_ERROR' in file <file_name> on line <n> at position <m>:
SQL access control error: Insufficient privileges to operate on schema '<schema_name>'

Cause

The role used to execute the statement does not have the privileges required to execute some or all of the statements in the file.

Solution

Use a role that has the appropriate privileges to execute the statements in the file. For more information, see Access Control Requirements.

See also: Stage Errors.

Examples

This example executes the file create-inventory.sql located in stage my_stage.

  1. Create a file named create-inventory.sql with the following statements:

    CREATE OR REPLACE TABLE my_inventory(
      sku VARCHAR,
      price NUMBER
    );
    
    EXECUTE IMMEDIATE FROM './insert-inventory.sql';
    
    SELECT sku, price
      FROM my_inventory
      ORDER BY price DESC;
    
    Copy
  2. Create a file named insert-inventory.sql with the following statements:

    INSERT INTO my_inventory
      VALUES ('XYZ12345', 10.00),
             ('XYZ81974', 50.00),
             ('XYZ34985', 30.00),
             ('XYZ15324', 15.00);
    
    Copy
  3. Create an internal stage my_stage:

    CREATE STAGE my_stage;
    
    Copy
  4. Upload both local files to the stage using the PUT command:

    PUT file://~/sql/scripts/create-inventory.sql @my_stage/scripts/
      AUTO_COMPRESS=FALSE;
    
    PUT file://~/sql/scripts/insert-inventory.sql @my_stage/scripts/
      AUTO_COMPRESS=FALSE;
    
    Copy
  5. Execute the create-inventory.sql script located in my_stage:

    EXECUTE IMMEDIATE FROM @my_stage/scripts/create-inventory.sql;
    
    Copy

    Returns:

    +----------+-------+
    | SKU      | PRICE |
    |----------+-------|
    | XYZ81974 |    50 |
    | XYZ34985 |    30 |
    | XYZ15324 |    15 |
    | XYZ12345 |    10 |
    +----------+-------+