Command Line Options

Command Line Options

Query Execution Options

/commandTimeout=<seconds>

Use this option to change the command execution timeout.

/connectionTimeout=<seconds>

Use this option to change the server connection timeout.

/noTransaction

This option disables the transaction mode of executing queries and scripts.

You may turn off the transaction mode, for example, for deleting database objects using scripts.

Otherwise, the scripts can be discarded by rollback if some of the deleted objects do not exist.

/inputCodePage=<codepage>

The option defines the input file code page.

Example:

/inputcodepage=65001

/outputCodePage=<codepage>

The option defines the output file code page.

Example:

/outputcodepage=1250

/outputCulture=<name>

The option defines the output culture.

Example:

/outputculture=en-GB

/noBOM

The option suppresses adding BOM to Unicode output.

Example:

/noBOM

/set=<parameter>=<value | function>[;...]

The option defines SQL statement and SQL script parameter values.

For example:

gsqlcmd rtd-mssql "SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @Table" /set=Table=RealTimeTables
gsqlcmd rtd-mysql "SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = :Table" /set=Table=real_time_tables

Use the following parameter declarations:

@Parameter for Microsoft SQL Server, Microsoft SQL Server Compact, and SQLite
:Parameter for MySQL, MariaDB, Oracle Database, IBM DB2, NuoDB, and PostgreSQL.

Do not use the /set option with scripts that contain parameter declarations in the SQL code.
For example, if the script contains codes for creating stored procedures and triggers.

gsqlcmd determines parameter types using actual value types: string, number, or datetime.

Use single quotes to define a string type for a number value. For example: '123' instead of 123.

Functions to use with /set and /add options

FunctionMeaning
UtcNow()Current UTC date and time
UtcDateTime()Current UTC date and time
UtcDate()Current UTC date
UtcTime()Current UTC time
Now()Current date and time
DateTime()Current date and time
Date()Current date
Time()Current time
NyseDateTime()NYSE trade date and time
NyseDate()NYSE trade date
NyseTime()NYSE trade time
FileDateTime(<File>)File date and time
FileDateTimeUtc(<File>)File UTC date and time
FileDateTimeNyse(<File>)File date and time as NYSE trade date and time
FileDate(<File>)File date
FileDateUtc(<File>)File UTC date
FileDateNyse(<File>)File date as NYSE trade date
FileTime(<File>)File time
FileTimeUtc(<File>)File UTC time
FileTimeNyse(<File>)File time as NYSE trade time
FileName(<File>)File name
FileNameWithoutExtension(<File>)File name without extension
FileText(<File>)File text
FileNameDateTime()Date and time parsed from an input file name *
FileNameDate()Date parsed from an input file name *
FileNameTime()Time parsed from an input file name *

* Supported formats for dates and times in file names:

The value after the first hyphenThe value at the name end
*-yyyyMMddHHmmss*.**yyyyMMddHHmmss.*
*-yyyyMMdd-HHmmss*.**yyyyMMdd-HHmmss.*
*-yyyyMMddTHHmmss*.**yyyyMMddTHHmmss.*
*-yyyy-MM-dd*.**yyyy-MM-dd.*
*-yyyyMMdd*.**yyyyMMdd.*
*-HHmmss*.**HHmmss.*

/fromFiles=<mask>

This option defines files used to expand SQL query parameters.

The SQL query must contain at least two parameters: filename and text.

These parameters get values from the file names and texts accordingly.

For example (SQL Server and MySQL forms):

gsqlcmd website.com "UPDATE content SET content = @text WHERE alias = @filename;" /fromFiles=content\*.htm

gsqlcmd website.com "UPDATE content SET content = :text WHERE alias = :filename;" /fromFiles=content\*.htm

You may use this feature in conjunction with the /toFiles option.

/trace

This option turns on tracing SQL commands sent to a server.

Use this mode to debug scripts or to measure the execution time of SQL commands.

By default, gsqlcmd writes trace messages to the console.

You may define the log file for trace messages in gsqlcmd.exe.config in the gsqlcmd home directory.

Common Output Options

/addRowNum

If the option is specified, gsqlcmd adds row numbers as the first column of the output.

/append

If the option is specified, gsqlcmd appends the data to the output file.
Otherwise, it replaces the existing file with a new one.

/asText | asCSV | asHTML

Use this option to specify the output format if the output file has an unknown extension.

/dateTimeFormat=<format>, /dateFormat=<format>, /timeFormat=<format>

Use this option to specify the formats for datetime values in the output.

See https://msdn.microsoft.com/en-us/library/zdtaw1bw(v=vs.100).aspx about the format string.

Example:

gsqlcmd rtd-db2 "SELECT * FROM RTD.QUOTES_YAHOO" QUOTES_YAHOO.CSV /datetimeformat=yyyy-MM-dd

Use double quotes to specify formats with spaces. For example:

"/datetimeformat=yyyy-MM-dd hh:mm:ss"

/noHeaders

The option disables adding headers to the output.

/toFiles

Use this option to export field values to separate files.

The SQL query output must contain exactly two fields used as a file name and a text.

The output file name parameter defines the output directory, the field used as a file name, and the file extension.

For example:

gsqlcmd website.com "SELECT alias, content FROM content" content\alias.htm /toFiles /outputCodepage=65001

This example exports the content field values to files with the alias field names.

You may use this feature in conjunction with the /fromFiles option.

CSV Options

/add=<header=value | function>[<separator>...]

This option defines additional data of the CSV output.

For example:

gsqlcmd rtd-sqlce "SELECT * FROM QuotesYahoo" QuotesYahoo.csv /add=File=QuotesYahoo.csv

In this example, the first column File will contain the value: QuotesYahoo.csv.

You may use the functions described below.

Functions for use with /set and /add options

FunctionMeaning
UtcNow()Current UTC date and time
UtcDateTime()Current UTC date and time
UtcDate()Current UTC date
UtcTime()Current UTC time
Now()Current date and time
DateTime()Current date and time
Date()Current date
Time()Current time
FileDateTime(<File>)File date and time
FileDate(<File>)File date
FileTime(<File>)File time
FileName(<File>)File name
FileNameWithoutExtension(<File>)File name without extension
FileText(<File>)File text
FileNameDateTime()Date and time parsed from an input file name *
FileNameDate()Date parsed from an input file name *
FileNameTime()Time parsed from an input file name *

* Supported formats for dates and times in file names:

The value after the first hyphenThe value at the name end
*-yyyyMMddHHmmss*.**yyyyMMddHHmmss.*
*-yyyyMMdd-HHmmss*.**yyyyMMdd-HHmmss.*
*-yyyyMMddTHHmmss*.**yyyyMMddTHHmmss.*
*-yyyy-MM-dd*.**yyyy-MM-dd.*
*-yyyyMMdd*.**yyyyMMdd.*
*-HHmmss*.**HHmmss.*

/[output]separator=<separator>|tab

The option defines the output CSV separator.

The default separator is a semicolon.

Use the Tab value to specify the tab.

For example:

gsqlcmd rtd-mysql "SELECT * FROM rtd.quotes_yahoo" quotes_yahoo.csv /separator=,

/inputSeparator=<separator>|tab

The option defines the input CSV separator.

/inputDateFormat=DMY|MDY

The option defines the input date format.

/firstRow=<first row>

The option defines the first data row in the input file.

/quoteChar=<char>

The option defines the quote character for string values.

The default value is a double quote. You may disable quotes using the option with an empty value: /QuoteChar=

/escapeChar=<char>

The option defines the escape character used to escape quote characters in string values.

The default value is a double quote. You may disable escaping using the option with an empty value: /EscapeChar=

HTML Output Options

/noTemplate

The option disables use of a template for HTML output.

/placeholder=<placeholder>

The option defines a placeholder to insert exported table data in the HTML output.

For example, if an HTML template contains the {sales} placeholder, you may use the option: /placeholder={Sales}

The default placeholder is {table}.

/template=<HTML template file>

The option defines a template for HTML output.

The template can contain the {table} placeholder to insert exported table data in the HTML output.

By default, gsqlcmd inserts the table data before the </body> tag.

The template can also contain the {title} placeholder to include the value of the title option. See below.

/title=<title>

The option defines a value for the {title} placeholder of the HTML template.

Code Generation Options

/Table=<target database table | view | stored procedure> | <SQL template file>

The option defines the target database table, view, stored procedure or SQL template file for code generation.

For example: /table=dbo.Payments

The option is obligatory for Insert, Update, Delete and Merge modes.

/fmt=<format file>

The option defines the format file for code generation.

gsqlcmd uses the file as a column name source instead of the CSV file.

The default value of this option is a file name of the CSV file with the .fmt extension.

/insertIdentity

The option defines including auto-generated identity columns into INSERT and MERGE SQL codes.

/insertNulls

The option defines including NULL values into generated INSERT SQL codes.

/singleLineSQL

The option defines generating single line INSERT and MERGE SQL codes.

/groupSize=<number of rows>

The option defines a number of rows separated by the GO batch separator.

/keys=<field>[,<field>[,...]]

The option defines fields used instead of the primary key fields in INSERT, UPDATE, and DELETE commands.
You may use this option, for example, to synchronize data between different databases using fields like email or SSN instead of identity fields.

/mssql | sqlce | mysql | oracle | db2 | nuodb | pgsql | sqlite

The option defines a target database platform for code generation.

Use this option if you do not specify the connection in positional parameters.

This website is using cookies. By continuing to browse, you give us your consent to our use of cookies as explained in our Cookie Policy.