Executing Scripts

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 may 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 may 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 with the create statements of stored procedures and triggers.

You may 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 may 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 may 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 databases 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 may turn off a transaction mode using the /noTransaction option.

You may use this, for example, for deleting database objects using scripts as some of the deleted objects may 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 may redirect the trace messages to a log file using the gsqlcmd.exe.config configuration file.