Using gsqlcmd

Using gsqlcmd

The gsqlcmd command line utility solves common database developer tasks:

  • Executing SQL scripts for creating, modifying, and deleting database objects (DDL) and data (DML).
  • Exporting database data into text, CSV, and HTML.
  • Preparing configuration files for importing CSV files into databases.
  • Generating INSERT, UPDATE, DELETE, and MERGE commands for CSV files.
  • Importing CSV files into databases.

gsqlcmd solves these tasks easier than native database command line utilities.

Moreover, it has the same syntax for all supported database platforms.

Command line formats:

gsqlcmd [execute] <connection> <sql query | file> [<output file>] [<options>]
gsqlcmd  prepare  <connection> <sql query | file> [<output file>] [<options>]
gsqlcmd  parse    <connection> <sql query | file> [<output file>] [<options>]

gsqlcmd  insert   <connection> <csv file>  [<output file>] [<options>]
gsqlcmd  update   <connection> <csv file>  [<output file>] [<options>]
gsqlcmd  delete   <connection> <csv file>  [<output file>] [<options>]
gsqlcmd  merge    <connection> <csv file>  [<output file>] [<options>]

gsqlcmd  fmt+                  <csv file>                  [<options>]
gsqlcmd  fmt                   <csv file>  [<output file>] [<options>]
gsqlcmd  create|bulk|ini|ace   <csv file>  [<output file>] [<options>]

gsqlcmd  localconfig | userconfig | appconfig
gsqlcmd  connections

gsqlcmd  help [chm]

See Command Line for a complete description.

You may use the exit codes in batch files.

Database Connections

You may specify a connection string inline. For example:

gsqlcmd "Provider=SQLOLEDB;Data Source=.\SQLEXPRESS;Initial Catalog=RTD;Integrated Security=SSPI" framework-install-en.sql

 However, gsqlcmd supports a better way.

You may specify the connection string in the configuration file and use it by name like

gsqlcmd rtd-mssql framework-install-en.sql

Moreover, you may create, edit, copy and delete connection strings in a visual mode using gConnectionManager:

gsqlcmd searches connection strings in the following configuration files:

  1. <current directory>\gsqlcmd.exe.config;
  2. %LocalAppData%\Gartle\gsqlcmd\gsqlcmd.exe.config;
  3. <installation folder>\gsqlcmd.exe.config.

Use the localConfig, userConfig, and appConfig modes to open the configuration files. For example:

gsqlcmd localconfig

Use the connections mode to list defined connection string names. For example:

gsqlcmd connections

Executing SQL Scripts

This example shows how to execute the setup.sql script against the named rtd-sqlce connection:

gsqlcmd rtd-sqlce setup.sql

This example shows how to execute the script using the OLEDB provider for Microsoft SQL Server:

gsqlcmd "Provider=SQLOLEDB;Data Source=.\SQLEXPRESS;Integrated Security=SSPI;Initial Catalog=RTD" setup.sql

This example shows how to execute the script using Microsoft SQL Server Compact .NET provider:

gsqlcmd "System.Data.SqlServerCe.4.0;Data Source=rtd.sdf" setup.sql

Moreover, gsqlcmd allows using SQL Server Compact and SQLite databases by file names. So, the following examples are valid:

gsqlcmd rtd.sdf setup.sql

gsqlcmd rtd.db setup.sql

This is an awesome feature if you work with embedded databases.

Exporting Database Data

This example executes the SQL statement and writes the output to the console:

gsqlcmd rtd-mysql "SELECT * FROM INFORMATION_SCHEMA.TABLES"

This command produces a plain text.

gsqlcmd writes output to the console and *.txt files as a plain text by default.

This command executes the export-quotes.sql script and writes the output to the quotes.csv file in the CSV format:

gsqlcmd rtd-db2 export-quotes.sql quotes.csv

gsqlcmd writes the output to *.csv files as CSV by default.

You may specify the output format explicitly. For example:

gsqlcmd rtd-db2 export-quotes.sql /asCSV >> quotes.csv

Also, you may tune the output using additional options like this:

gsqlcmd rtd-db2 export-quotes.sql quotes.csv /separator=, /datetimeformat=yyyy-MM-dd

This example produces an HTML output:

gsqlcmd rtd-ora export-quotes.sql quotes.htm

gsqlcmd uses the built-in HTML template by default. You may specify your template like this:

gsqlcmd rtd-ora export-quotes.sql quotes.htm /template=table-template.htm

You may turn off the use of the template with the /notemplate option.

Using Variables in SQL Scripts

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

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

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, 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.

Internal Script Commands

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

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

It is a useful feature for monitoring script execution progress.

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

For example:

-- print Table ColumnTranslation has been created

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.

Importing CSV Data into Databases

gsqlcmd allows generating the following files for importing CSV data into databases:

  • CREATE TABLE SQL statements.
  • Format files for OPENROWSET(BUCK...) import (SQL Server only).
  • INI files for ODBC drivers.
  • INSERT INTO ... SELECT statements for OPENROWSET(BUCK...) import (SQL Server only).
  • INSERT INTO ... SELECT statements for OPENROWSET using MICROSOFT.ACE.OLEDB.12.0 (SQL Server only).
  • INSERT statements.
  • INSERT and UPDATE statements.

Creating Format Files

Originally, format files are used with the Microsoft SQL Server bcp command line utility and the OPENROWSET(BUCK...) function.

gsqlcmd allows generating such files and uses them as column name data sources in other modes.

So, you may generate the format file first, edit it, and then generate other files.

For example, the source CSV file, payments.csv, has the following data:

ID;Date;Sum;"Account Name";"Company Name";"Item Name";"Comment"
1;01/10/2017;200000;"My Bank";"Rose, Inc";"Revenue";""
2;01/10/2017;-50000;"My Bank";"Land, Inc";"Expenses";""

You may generate the format file using the fmt mode:

gsqlcmd fmt payments.csv payments.fmt

and edit the column names (remove spaces, for example):

9.0
7
1   SQLCHAR   0  255  ";"          1   "ID"                           ""
2   SQLCHAR   0  255  ";"          2   "Date"                         ""
3   SQLCHAR   0  255  ";\""        3   "Sum"                          ""
4   SQLCHAR   0  255  "\";\""      4   "AccountName"                  ""
5   SQLCHAR   0  255  "\";\""      5   "CompanyName"                  ""
6   SQLCHAR   0  255  "\";\""      6   "ItemName"                     ""
7   SQLCHAR   0  255  "\"\r\n"     7   "Comment"                      ""

You may disable column import replacing the column index (column 6) with 0.

Creating Database Tables

You may generate the CREATE TABLE statement:

gsqlcmd create payments.csv payments.create.sql /table=dbo.Payment /fmt=payments.fmt

You may specify the target database platform using the options: /mssql, /sqlce, /mysql, /oracle, /db2, /nuodb, /pgsql, or /sqlite.

Here is the result in payments.create.sql:

CREATE TABLE [dbo].[Payment] (
    [ID] int IDENTITY(1,1) NOT NULL,
    [Date] datetime NULL,
    [Sum] int NULL,
    [AccountName] nvarchar(255) NULL,
    [CompanyName] nvarchar(255) NULL,
    [ItemName] nvarchar(255) NULL,
    [Comment] nvarchar(255) NULL,
  CONSTRAINT [PK_Payment_dbo] PRIMARY KEY CLUSTERED (
    [ID] ASC
  )
)
GO
print 'Table [dbo].[Payment] has been created'
GO

Verify and change column data types. In the example, the [Sum] column created as int as the source column contains the integer values only.
You may change int to money or to float, for example.

Then you may execute the CREATE TABLE code in the execute mode:

gsqlcmd execute <Connection> payments.create.sql

Importing CSV Data into Microsoft SQL Server using BCP

You may import CSV data into Microsoft SQL Server using the bcp command line utility included in Microsoft SQL Server client packages.

For example, you may import CSV data from payments.csv into the dbo.Payment table using the following command:

bcp "dbo.Payment" in payments.csv -S . -d <database> -T -f payments.fmt -E

The -E option keeps identity values.

The bcp utility does not support the UTF-8 encoding.
So, you may convert CSV data into Windows ANSI encoding or to use methods described below.

See details about the bcp utility at https://msdn.microsoft.com/en-us/library/ms162802.aspx.

Creating and Using OPENROWSET(BUCK...) Statements

To generate the INSERT INTO ... SELECT statement, use the following command:

gsqlcmd bulk payments.csv payments.insert.bulk.sql /table=dbo.Payment /fmt=payments.fmt /insertIdentity

There is the result:

SET IDENTITY_INSERT dbo.Payment ON;
INSERT INTO dbo.Payment
    ( [ID]
    , [Date]
    , [Sum]
    , [AccountName]
    , [CompanyName]
    , [ItemName]
    , [Comment]
    )
SELECT
    t.[ID]
    , t.[Date]
    , t.[Sum]
    , t.[AccountName]
    , t.[CompanyName]
    , t.[ItemName]
    , t.[Comment]
FROM
    (
    SELECT
        [ID] AS [ID]
        , [Date] AS [Date]
        , [Sum] AS [Sum]
        , [AccountName] AS [AccountName]
        , [CompanyName] AS [CompanyName]
        , [ItemName] AS [ItemName]
        , [Comment] AS [Comment]
    FROM
        OPENROWSET(
            BULK 'D:\payments.csv',
            FORMATFILE = 'D:\payments.fmt',
            CODEPAGE = '1250',
            FIRSTROW = 2) t
    ) t
SET IDENTITY_INSERT dbo.Payment OFF;

If you do not specify the /table option, gsqlcmd does not generate the INSERT INTO header.

If you specify the /insertIdentity option, gsqlcmd generates the SET IDENTITY_INSERT statements to insert the identity column from the source CSV file.
You may skip the source identity column specifying 0 in column 6 of the format file.

The OPENROWSET function requires absolute paths of the CSV and format files.
You have to edit paths to your real file locations.

You may import the data using this file if the paths are available for your SQL Server instance.

You may execute the code using SQL Server Management Studio, the sqlcmd utility, or gsqlcmd like:

gsqlcmd execute <Connection> payments.insert.bulk.sql

Also, you may insert this code into a stored procedure.

You may check loaded data using the inline SELECT statement. For example:

gsqlcmd execute <Connection> "SELECT * FROM dbo.Payment"
The result is:
ID Date          Sum AccountName CompanyName ItemName Comment
 1 01/10/2017 200000 My Bank     Rose, Inc   Revenue
 2 01/10/2017 -50000 My Bank     Land, Inc   Expenses

Creating SCHEMA.INI Sections for Using with ODBC

You may easily create schema.ini file sections used with ODBC.

For example, execute:

gsqlcmd ini payments.csv payments.ini /fmt=payments.fmt

Here is the payments.ini result:

[payments.csv]
ColNameHeader=True
Format=Delimited(;)
MaxScanRows=100
CharacterSet=1250
Col1="ID" Char Width 255
Col2="Date" Char Width 255
Col3="Sum" Char Width 255
Col4="AccountName" Char Width 255
Col5="CompanyName" Char Width 255
Col6="ItemName" Char Width 255
Col7="Comment" Char Width 255

Copy and paste these rows into the required schema.ini file.

Creating and Using OPENROWSET with MICROSOFT.ACE.OLEDB.12.0

The bcp utility and the OPENROWSET(BUCK...) function do not support the UTF-8 encoding.

As a solution, you may use the MICROSOFT.ACE.OLEDB.12.0 provider with the OPENROWSET function.

You may download the MICROSOFT.ACE.OLEDB.12.0 provider at
https://www.microsoft.com/en-us/download/details.aspx?id=13255

To generate the SQL code, use the following command:

gsqlcmd ace payments.csv payments.insert.ace.sql /table=dbo.Payment /fmt=payments.fmt

Here is the payments.insert.ace.sql result:

INSERT INTO dbo.Payment
    ( [ID]
    , [Date]
    , [Sum]
    , [AccountName]
    , [CompanyName]
    , [ItemName]
    , [Comment]
    )
SELECT
    t.[ID]
    , t.[Date]
    , t.[Sum]
    , t.[AccountName]
    , t.[CompanyName]
    , t.[ItemName]
    , t.[Comment]
FROM
    (
    SELECT
        [ID] AS [ID]
        , [Date] AS [Date]
        , [Sum] AS [Sum]
        , [AccountName] AS [AccountName]
        , [CompanyName] AS [CompanyName]
        , [ItemName] AS [ItemName]
        , [Comment] AS [Comment]
    FROM
        OPENROWSET('MICROSOFT.ACE.OLEDB.12.0',
            'Text;Database=D:\;HDR=Yes;Format=Delimited(;)',
            'SELECT * FROM [payments.csv]') t
    ) t

To use this code, you have to insert [payments.csv] section into the schema.ini file. See the previous topic.

You may execute the code using SQL Server Management Studio, the native sqlcmd utility, or gsqlcmd like this:

gsqlcmd execute <Connection> payments.insert.ace.sql

Also, you may insert this code into a stored procedure.

Creating INSERT Statements for Importing CSV Data

You may generate INSERT statements to insert CSV data into databases.

For example:

gsqlcmd insert AzureDemo payments.csv payments.insert.sql /table=dbo.Payment /fmt=payments.fmt

Here is the result in payments.insert.sql:

INSERT INTO [dbo].[Payment] ([Date], [Sum], [AccountName], [CompanyName], [ItemName])
   VALUES ('20140110 00:00:00.000', 200000, N'My Bank', N'Rose, Inc', N'Revenue');
INSERT INTO [dbo].[Payment] ([Date], [Sum], [AccountName], [CompanyName], [ItemName])
   VALUES ('20140110 00:00:00.000', -50000, N'My Bank', N'Land, Inc', N'Expenses');

gsqlcmd requires a connection as the first parameter in the insert mode. The example uses the AzureDemo connection.

gsqlcmd loads the target table definition and generates the code specific to the target database platform.

You may specify the /insertIdentity option to insert identity values from the source CSV file.

Use the execute mode to execute the generated INSERT commands:

gsqlcmd execute AzureDemo payments.insert.sql

The free gsqlcmd version allows generating INSERT statements for CSV files with less that 50 rows.

You may purchase gsqlcmd Personal or gsqlcmd Enterprise to remove this limit.

Creating INSERT and UPDATE Statements for Merging CSV Data

You may generate INSERT and UPDATE statements to merge CSV data into the desired table.

For example:

gsqlcmd merge AzureDemo payments.csv payments.merge.sql /table=dbo.Payment /fmt=payments.fmt

The result in payments.merge.sql is:

UPDATE [dbo].[Payment]
SET
  [Date] = s.[Date]
  , [Sum] = s.[Sum]
  , [AccountName] = s.[AccountName]
  , [CompanyName] = s.[CompanyName]
  , [ItemName] = s.[ItemName]
  , [Comment] = s.[Comment]
FROM
  [dbo].[Payment] t INNER JOIN (
  SELECT 1 AS [ID], '20140110 00:00:00.000' AS [Date], 200000 AS [Sum], N'My Bank' AS [AccountName],
    N'Rose, Inc' AS [CompanyName], N'Revenue' AS [ItemName], NULL AS [Comment]
  UNION ALL SELECT 2 AS [ID], '20140110 00:00:00.000' AS [Date], -50000 AS [Sum], N'My Bank' AS [AccountName],
    N'Land, Inc' AS [CompanyName], N'Expenses' AS [ItemName], NULL AS [Comment]
) s ON t.[ID] = s.[ID];

INSERT INTO [dbo].[Payment] ([Date], [Sum], [AccountName], [CompanyName], [ItemName], [Comment]) SELECT s.[Date],
  s.[Sum], s.[AccountName], s.[CompanyName], s.[ItemName], s.[Comment]
FROM (
  SELECT s.[ID], s.[Date], s.[Sum], s.[AccountName], s.[CompanyName], s.[ItemName], s.[Comment]
  FROM (
    SELECT 1 AS [ID], '20140110 00:00:00.000' AS [Date], 200000 AS [Sum], N'My Bank' AS [AccountName],
      N'Rose, Inc' AS [CompanyName], N'Revenue' AS [ItemName], NULL AS [Comment]
    UNION ALL SELECT 2 AS [ID], '20140110 00:00:00.000' AS [Date], -50000 AS [Sum], N'My Bank' AS [AccountName],
      N'Land, Inc' AS [CompanyName], N'Expenses' AS [ItemName], NULL AS [Comment]
    ) s
    LEFT OUTER JOIN [dbo].[Payment] t ON t.[ID] = s.[ID] WHERE t.[ID] IS NULL
  ) s;
GO
print 'Processed 2 total records';

You may use the /singleLineSQL option to generate single line commands.

You may specify the /insertIdentity option to insert identity values from the source CSV file.

Use the execute mode to execute the generated commands:

gsqlcmd execute AzureDemo payments.merge.sql

The free gsqlcmd version allows generating MERGE statements for CSV files with less that 50 rows.

You may purchase gsqlcmd Personal or gsqlcmd Enterprise to remove this limit. 

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.