Executing Scripts with gsqlcmd

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

Use the exec mode in the following form:

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

where input:

<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

You can use a file name for SQLite and SQL Server Compact databases like

gsqlcmd exec test.db install.sql

gsqlcmd exec test.sdf 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, SQL Server Compact, and SQLite,
and as :<Parameter> for MySQL, MariaDB, Oracle Database, IBM DB2, NuoDB, Snowflake, and PostgreSQL.

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

You can load script variables from task files.

For example, task.txt has the following content:

P1 P2
1  2
1  3

The test script.sql contains the following content:

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

So, the following command produces the following result:

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

1 + 2 = 3
1 + 3 = 4

As you can see, gsqlcmd executes the script for each line of the task file.

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

So, you can execute scripts with parameters values from a database like

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

Internal Script Commands

SQL scripts can contain the -- print <Message> commands.

For example:

-- print Table ColumnTranslation created

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

This feature works with all database servers in the same manner even a server does not support print commands.

Transaction Modes

gsqlcmd executes SQL commands in the ReadCommitted transaction isolation level.

So, the default rule is "all or nothing."

You can turn off a transaction mode using the /noTransaction option.

You can use this, for example, for deleting database objects using scripts, as some of the deleted objects can not exist.

Trace Mode

If a script has errors, turn on the trace mode using the /trace option.

By default, gsqlcmd writes trace messages to the console.

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