Execute SQL Scripts with gsqlcmd

gsqlcmd supports executing native SQL scripts for any supported database platform.

Use the exec mode in the following format:

gsqlcmd [exec] <connection> [<input> [<output>]]

Where input can be one of the following:

<query> | <file> | <mask> | @[<task>] | <command> | <table> | <view>

Here are several examples:

gsqlcmd exec db install.sql

gsqlcmd exec db install.sql result.txt

gsqlcmd exec db install.sql.gz

gsqlcmd exec db install-*.sql

gsqlcmd exec db install.zip

gsqlcmd exec db @install-task.txt

For SQLite databases, you can use a file name like this:

gsqlcmd exec test.db install.sql

Using Variables in SQL Scripts

You can define parameter values for SQL commands and scripts using the /set option.

For example:

gsqlcmd exec db "EXEC dbo.usp_test @param1, @param2" /set=Param1=123;Param2='abc'

Declare script parameters as @<Parameter> for Microsoft SQL Server and SQLite,
and as :<Parameter> for MySQL, MariaDB, Oracle Database, Snowflake, and PostgreSQL.

Avoid using the /set option with scripts that declare variables calculated by database servers.
For instance, do not use /set to create stored procedures and triggers.

You can load script variables from task files.

For example, if task.txt contains:

P1 P2
1  2
1  3

And test script.sql contains:

SELECT @p1, '+', @p2, '=', @p1 + @p2

Then the following command produces this result:

gsqlcmd exec master script.sql /taskfile=task.txt /noHeaders

1 + 2 = 3
1 + 3 = 4

As shown, gsqlcmd executes the script for each line of the task file.

gsqlcmd Enterprise allows retrieving task values from a database using the <connection>::<query> format.

You can execute scripts with parameter values from a database like this:

gsqlcmd exec master script.sql "/taskfile=master::SELECT p1, p2 FROM ..." /noHeaders

Internal Script Commands

SQL scripts can include -- print <Message> commands.

For example:

-- print Table ColumnTranslation created

gsqlcmd executes these commands internally and writes messages to the output.

This feature works consistently across all database servers, even if a server does not support print commands.

Transaction Modes

gsqlcmd executes SQL commands in the ReadCommitted transaction isolation level.

The default behavior is "all or nothing."

You can disable transaction mode using the /noTransaction option.

This is useful, for example, when deleting database objects, as some of the deleted objects may not exist.

Trace Mode

If a script encounters errors, enable trace mode using the /trace option.

By default, gsqlcmd writes trace messages to the console.

You can redirect these trace messages to a log file using the gsqlcmd.exe.config configuration file.