gsqlcmd

gsqlcmd is a command line utility for working with databases. It allows:

Executing SQL commands and scripts with parameters.
Exporting data from databases into text, CSV, and HTML.
Preparing configuration files for importing CSV files into databases.
Generating INSERT, UPDATE, DELETE, and MERGE commands for CSV data.
Importing CSV files into databases.

gsqlcmd allows working with different database servers in the same manner and performing usual developer tasks with fewer efforts.

gsqlcmd supports the following database platforms:

Microsoft Azure SQL Database
Microsoft SQL Server
Microsoft SQL Server Compact
Oracle Database
IBM DB2
MySQL and MariaDB
NuoDB
PostgerSQL
SQLite

You may start learning about gsqlcmd on the following topics:

Using gsqlcmd
Importing CSV Data into Databases
Edition Comparison
Command Line
Connection Manager
Configuration File

Version 4.11, February 21, 2018

New features:

The FirstRow parameter allows setting the first data row in an input CSV.
The InputDateFormat parameter allows setting input date formats like DMY or MDY.
The OutputCulture parameter allows setting output regional settings.
For example: OutputCulture=en-GB

Improvements:

The 'Generating SQL Server Install Scripts' application includes updates for SaveToDB Framework 8.

Bug fixes:

Fixed System.OverflowException with MySQL.

Version 4.10, February 7, 2018

Improvements:

Updated Product Registration Wizard

Version 4.9, January 15, 2018

New features:

The Skype Reports example shows how to import Skype calls into a SQL Server database.

Improvements:

The utility uses the new line characters from the templates if possible.

Bug fixes:

Fixed removing leading zeros in fields of variant types.
Fixed exceptions with converting 0000-00-00 00:00:00 to dates in MySQL if Allow Zero Datetime=False or Convert Zero Datetime=False.

Version 4.8, December 21, 2017

Bug fixes:

Fixed issues with removing leading zeros from string values

Version 4.7, November 3, 2017

Bug fixes:

Registration wizard WebException in the offline mode

Version 4.6, August 22, 2017

Breaking changes:

Changed .NET platform from .NET Framework 4.0 Client Profile to .NET Framework 4.5.2.
Changes in requirements for Windows 7, Windows Server 2008, and Windows Server 2008 R2.
Removed support of Windows XP.
Removed support of SQL Server 2000.
Removed support of MySQL 5.0, 5.1, 5.2.
Removed support of MariaDB 5.1, 5.2, 5.3.

Bug Fixes:

Issues with registering products.

Version 4.5, August 1, 2017

New features:

Azure Active Directory integrated authentication
Azure Active Directory password authentication
/NoBOM option to suppress adding BOM to output

Improvements:

The application for generating SQL Server install scripts uses utf-8 encoding

Version 4.4, July 5, 2017

New features:

Improvements:

Updated registration wizard
Updated installer

Bug Fixes:

Converting IP-addresses to numbers

Version 4.3, May 31, 2017

New features:

New functions of the /add and /set options: FileNameDateTime(), FileNameDate(), and FileNameTime().
New options to export values to text files and import values from the text files: /toFiles and /fromFiles=<mask>
For example:
gsqlcmd website.com "SELECT alias, content FROM content" content\alias.htm /toFiles /outputCodepage=65001
gsqlcmd website.com "UPDATE content SET content = @text WHERE alias = @filename;" /fromFiles=content\*.htm
See usage in the new 'Website Content Editor' example.
New usage examples:
Website Content Editor
Website CSV Editor

Improvements:

Updated gConnectionManager 3.3

Bug Fixes:

Skipped empty values in the columns added using the /add option.
Ignored specified date and time formats for data with multiple date and time columns.

Version 4.2, May 11, 2017

Licensing changes

Use of gsqlcmd requires registration.
You may activate the trial version and register the free edition after the trial.
Reduced limits of the free version: 50 generated commands based on CSV data.
You may remove this limit purchasing gsqlcmd subscription starting $25 per year per PC.
See Edition Comparison.

Improvements:

Rewritten and redesigned documentation
Updated installer
Removed portable package
Support of ODBC Driver 13 for SQL Server
Updated gConnectionManager 3.2

Bug Fixes:

Detecting wrong data types for user-defined types in SQL Server

Version 4.1, February 3, 2017

Bug Fixes:

Fixed possible issues with detecting primary keys in SQL Server.
The bug was added in gsqlcmd 4.0.

Version 4.0.3, January 25, 2017

Bug Fixes:

Fixed issues with creating SQLite databases

Version 4.0, January 17, 2017

You may upgrade previous versions to gsqlcmd 4.0 for free.

New features:

gsqlcmd supports SQLite databases.
The CreateSQLite3 mode allows creating new SQLite database files.
The /keys option allows defining fields used instead of 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.
gsqlcmd is available as a subscription.

Improvements:

Updated gConnectionManager 3.0
Significantly improved performance of executing MySQL commands

gsqlcmd allows easily executing the following tasks:

Executing SQL queries and scripts with parameters.
Exporting database data into CSV, HTML, and text.
Preparing configuration files for importing CSV files into databases.
Generating INSERT, UPDATE, DELETE, and MERGE commands for CSV files.
Importing CSV files into databases.

This one tool can replace sqlcmd, bcp, sqlplus, db2, and mysql in the most scenarios with additional benefits.
It allows getting results with fewer efforts and for all major database platforms in the same way.

Feature	Free	Personal	Enterprise
Features available on all supported database platforms
Executing SQL queries and scripts with parameters	✓	✓	✓
Exporting data into CSV, HTML, and text	✓	✓	✓
Exporting and importing files using /toFiles and /fromFiles options	50 files	✓	✓
Generating CREATE TABLE statements for importing CSV files	✓	✓	✓
Generating INSERT, UPDATE, DELETE and MERGE commands for CSV files	50 rows	✓	✓
Generating SQL commands using SQL templates for CSV files	50 rows	✓	✓
Generating execute stored procedure commands for CSV files	50 rows	✓	✓
Generating schema.ini for importing CSV files using ODBC	✓	✓	✓
Using named connection strings, opened and encrypted	✓	✓	✓
Supported database servers: SQL Server, SQL Server Compact, MySQL, Oracle, DB2, NuoDB, PostgreSQL, SQLite	✓	✓	✓
Features specific to Microsoft SQL Server
Generating format files and SQL codes for OPENROWSET(BULK...)	✓	✓	✓
Generating SQL codes for OPENROWSET using MICROSOFT.ACE.OLEDB.12.0	✓	✓	✓
Licensing
Commercial use	✓	x	✓

Installing

To install gsqlcmd, unzip the downloaded gsqlcmd package and run setup.exe.

Log off and log on to activate a new PATH environment variable.

Now, you may run the command prompt and type: gsqlcmd

Updating

To update gsqlcmd, unzip the downloaded gsqlcmd package and run setup.exe.

Uninstalling

To uninstall gsqlcmd, open Control Panel, Programs and Features, then select gsqlcmd and click the Uninstall button.

Supported Architectures:

x86, x64

Supported Operating Systems:

Windows Vista SP2, 7 SP1, 8, 8.1, 10
Windows Server 2008 SP2, 2008 R2 SP1, 2012, 2012 R2, 2016

Supported Versions of Microsoft SQL Server:

Microsoft SQL Server 2005, 2008, 2008 R2, 2012, 2014, 2016 including Express LocalDB
Microsoft Azure SQL Database

Supported Versions of Microsoft SQL Server Compact:

Microsoft SQL Server Compact 3.5, 4.0

Supported Versions of Oracle Database:

Oracle Database 10g R1/R2, Oracle Database 11g R1/R2, Oracle Database 12c R1

Supported Versions of IBM DB2:

IBM DB2 9.5, 9.7, 9.8, 10.1, 10.5, 11.1

Supported Versions of MySQL:

MySQL 5.5, 5.6, 5.7

Supported Versions of SkySQL MariaDB:

MariaDB 5.5, 10.0, 10.1, 10.2

Supported Versions of NuoDB:

NuoDB 2.0.4, 2.1, 2.2, 2.3, 2.4, 2.5, 2.6

Supported Versions of PostgreSQL:

PostgreSQL 8.0, 8.1, 8.2, 8.3, 8.4, 9.0, 9.1, 9.2, 9.3, 9.4

Supported Versions of SQLite:

SQLite 2, 3

This End-User License Agreement (EULA) is a legal agreement between you (either an individual or a single entity) and Gartle Technology Corporation for any gsqlcmd software, use examples and documentation (Software) that accompany this EULA.

YOU AGREE TO BE BOUND BY THE TERMS OF THIS EULA BY INSTALLING, COPYING, OR OTHERWISE USING THE SOFTWARE.

IF YOU DO NOT AGREE, DO NOT INSTALL, COPY, OR USE THE SOFTWARE.

Your licensing of Software is in accordance with the terms of the EULA in effect at the time of such licensing. By licensing Software, you accept and agree to the EULA in effect at such time.

GRANT OF LICENSE: Gartle Technology Corporation grants you the following rights provided that you comply with all terms and conditions of this EULA:
1. Trial Period: You may install, use, access, display and run the Software for purposes of evaluation of the Software for a period of fifteen (15) days from the date of installation of the Software to determine if the Software meets your needs. Upon completion of your evaluation or at the end of the 15-day trial period, whichever occurs first, you must do one of four things: either
  1) register the gsqlcmd Free edition in order to continue use of Software;
  2) purchase license for the gsqlcmd Personal or Enterprise edition in order to continue use of Software;
  3) purchase a subscription for the gsqlcmd Personal or Enterprise edition in order to continue use of Software;
  or 4) discontinue use of the Software, and uninstall the Software from any and all machines.
2. Installation and Use: You may install, use, access, display and run the Software free of charge on a non-exclusive basis and without right of sublicense.
  The free version has several limits. You may purchase and register gsqlcmd Personal or Enterprise edition to remove limits. The commercial use of the gsqlcmd Personal edition is not permitted.
3. Software Transfer: You may transfer the Software to a different internal workstation or user so long as you have purchased a License for each such workstation or user. You may not, however, transfer the Software to a Third Party.
4. Use of Examples: You may install, access, modify and use Software examples for your private or company-internal purposes.
LIMITATIONS: You may not use, copy, modify, display, rent, lease, loan, transfer, distribute, download, merge, or make any translation or derivative work of the Software, except as expressly provided herein. You may not reverse engineer, decompile, or disassemble the Software, except and only to the extent that such activity is expressly permitted by applicable law notwithstanding this limitation.
INTELLECTUAL PROPERTY RIGHTS AND CONFIDENTIALITY: The Software, including methods, processes and/or techniques utilized therein, is owned by, proprietary to and valuable trade secrets of Gartle Technology Corporation and is protected by Russian Federation copyright law and international treaties. You agree to take no actions that impair or infringe Gartle Technology Corporation's intellectual property rights in the Software. You agree not to remove, efface or obscure any copyright notices, other proprietary markings or confidentiality legends placed upon or contained within the Software.
DISCLAIMER OF WARRANTIES: Gartle Technology Corporation disclaims all warranties concerning the Software and Services (if any), express, implied, or statutory, including without limitation, any warranties, duties or conditions of merchantability or fitness for a particular purpose, warranties of reliability or availability, of accuracy or completeness of responses, of results, of workmanlike effort, of lack of viruses, and of lack of negligence, all with regard to the Software, and the provision of or failure to provide support or other services, information, software, and related content through the Software or otherwise arising out of the use of the Software. Gartle Technology Corporation does not warrant that the Software will operate in combination with other software products selected by you, or that the Software will operate uninterrupted or error-free. Additionally, Gartle Technology Corporation and its suppliers provide the Software and Services AS IS AND WITH ALL FAULTS. THERE IS NO WARRANTY OR CONDITION OF TITLE, QUIET ENJOYMENT, QUIET POSSESSION, CORRESPONDENCE TO DESCRIPTION, OR NONINFRINGEMENT WITH REGARD TO THE SOFTWARE.
NO LIABILITY: TO THE MAXIMUM EXTENT PERMITTED BY APPLICABLE LAW, IN NO EVENT SHALL GARTLE TECHNOLOGY CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY SPECIAL, INCIDENTAL, PUNITIVE, INDIRECT, OR CONSEQUENTIAL DAMAGES WHATSOEVER (INCLUDING, BUT NOT LIMITED TO, DAMAGES FOR LOSS OF PROFITS OR CONFIDENTIAL OR OTHER INFORMATION, FOR LOSS OF DATA, FOR BUSINESS INTERRUPTION, FOR PERSONAL INJURY, FOR LOSS OF PRIVACY, FOR FAILURE TO MEET ANY DUTY INCLUDING OF GOOD FAITH OR OF REASONABLE CARE, FOR NEGLIGENCE, AND FOR ANY OTHER PECUNIARY OR OTHER LOSS WHATSOEVER) ARISING OUT OF OR IN ANY WAY RELATED TO THE USE OF OR INABILITY TO USE THE SOFTWARE, THE PROVISION OF OR FAILURE TO PROVIDE SUPPORT OR OTHER SERVICES, INFORMATION, SOFTWARE, AND RELATED CONTENT THROUGH THE SOFTWARE OR OTHERWISE ARISING OUT OF THE USE OF THE SOFTWARE, OR OTHERWISE UNDER OR IN CONNECTION WITH ANY PROVISION OF THIS EULA, EVEN IN THE EVENT OF THE FAULT, TORT (INCLUDING NEGLIGENCE), MISREPRESENTATION, STRICT LIABILITY, BREACH OF CONTRACT OF GARTLE TECHNOLOGY CORPORATION OR ANY SUPPLIER, AND EVEN IF GARTLE TECHNOLOGY CORPORATION OR ANY SUPPLIER HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES.
LIMITATION ON REMEDIES: Within the first thirty (30) days after your receipt of the Software, should you encounter and report to Gartle Technology Corporation within such time period a reproducible error that causes the Software not to perform in all material respects as set forth in the Software documentation, then Gartle Technology Corporation will, at its sole discretion, either: a) resolve the error or malfunction, and modify or replace the Software (if deemed necessary by Gartle Technology Corporation); or b) allow you to terminate this EULA with respect to the non-conforming Software and, upon your return of the Software to Gartle Technology Corporation, Gartle Technology Corporation shall provide you with the lesser of a) the actual damages incurred by you; or b) the amount you paid for the non-conforming Software. The remedies described in this section shall be your sole and exclusive remedies under this EULA.
GENERAL PROVISIONS
1. Reservation of Rights and Ownership: Gartle Technology Corporation reserves all rights not expressly granted to you in this EULA. The Software is licensed, not sold.
2. Consent to Use of Data: You agree that Gartle Technology Corporation and its affiliates may collect and use technical information gathered as part of the Software support services provided to you, if any, related to the Software. Gartle Technology Corporation may use this information solely to improve Gartle Technology Corporation products or to provide customized services or technologies to you and will not disclose this information in a form that personally identifies you.
3. Links to Third Party Sites: We may link to third-party sites through the use of the Software. The third-party sites are not under the control of Gartle Technology Corporation, and Gartle Technology Corporation is not responsible for the contents of any third-party sites, any links contained in third-party sites, or any changes or updates to third-party sites. Gartle Technology Corporation is not responsible for webcasting or any other form of transmission received from any third-party sites. Gartle Technology Corporation is providing these links to third-party sites to you only as a convenience, and the inclusion of any link does not imply an endorsement by Gartle Technology Corporation of the third-party site.
4. Additional Software/Services: This EULA applies to updates, supplements, add-on components, or Internet-based services components, of the Software that Gartle Technology Corporation may provide to you or make available to you after the date you obtain your initial copy of the Software, unless Gartle Technology Corporation provides other terms along with the update, supplement, add-on component, or Internet-based services component. Gartle Technology Corporation reserves the right to discontinue any Internet-based services provided to you or made available to you through the use of the Software.
5. Upgrades: To use Software identified as an upgrade, you must first be licensed for the software identified by Gartle Technology Corporation as eligible for the upgrade. After upgrading, you may no longer use the software that formed the basis for your upgrade eligibility.
6. Applicable Law: This EULA is governed by the laws of the Russian Federation. Any legal action or proceeding relating to this EULA shall be instituted in a court of arbitration in the Moscow City, Russian Federation. Gartle Technology Corporation and you agree to submit to the jurisdiction of, and agree that venue is proper in, these courts in any such action or proceeding. The prevailing party in any action to enforce this EULA will be entitled to recover its attorney fees and costs in connection with such action.
7. Waiver: The failure of either party to enforce any of the terms of this EULA shall not be construed as a waiver of future enforcement of that or any other term.
8. Entire Agreement and Severability: This EULA (including any addendum or amendment to this EULA which is included with the Software) is the entire agreement between you and Gartle Technology Corporation relating to the Software and the support services (if any) and it supersedes all prior or contemporaneous oral or written communications, proposals and representations with respect to the Software or any other subject matter covered by this EULA. To the extent the terms of any Gartle Technology Corporation policies or programs for support services conflict with the terms of this EULA, the terms of this EULA shall control. If any provision of this EULA is held to be void, invalid, unenforceable or illegal, the other provisions shall continue in full force and effect.
9. Termination: Without prejudice to any other rights, Gartle Technology Corporation may terminate this EULA if you fail to comply with the terms and conditions of this EULA. In such event, you must destroy all copies of the Software and all of its component parts and you will not be entitled to any refund of monies.

You can use gsqlcmd Enterprise during a 15-day trial period for free.

You have to activate the trial.

When the trial period has expired, you have the following options:

To register the gsqlcmd Free edition for free.
To purchase a license and register the gsqlcmd Personal or Enterprise edition.
To purchase a subscription and register the gsqlcmd Personal or Enterprise edition.
To stop the use and remove gsqlcmd from a computer.

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:

<current directory>\gsqlcmd.exe.config;
%LocalAppData%\Gartle\gsqlcmd\gsqlcmd.exe.config;
<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 http://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
http://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.

Command Line Modes

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  createSqlCe35|createSqlCe40 <file>
gsqlcmd  createSQLite3               <file>

gsqlcmd  localconfig | userconfig | appconfig
gsqlcmd  connections

gsqlcmd  stamp [<datetime format>]

gsqlcmd  register

gsqlcmd  help  [chm]

<Connection> Format

<ConnectionString name>              ConnectionString name from gsqlcmd.exe.config
or <ConnectionString>                ConnectionString for OLEDB, ODBC, and DSN
or <ProviderName>;<ConnectionString> ConnectionString for .NET providers

Query Execution Options

/commandTimeout=<seconds>
/connectionTimeout=<seconds>
/noTransaction
/inputCodepage=<codepage>
/outputCodepage=<codepage>
/outputCulture=<name>
/noBOM
/set=<parameter>=<value | function>[;...]
/trace
/fromFiles=<mask>

Functions for /set and /add parameters

UtcNow() | UtcDateTime()
UtcDate()
UtcTime()
Now() | DateTime()
Date()
Time()
NyseDateTime()
NyseDate()
NyseTime()
FileDateTime(<file>)
FileDateTimeUtc(<file>)
FileDateTimeNyse(<file>)
FileDate(<file>)
FileDateUtc(<file>)
FileDateNyse(<file>)
FileTime(<file>)
FileTimeUtc(<file>)
FileTimeNyse(<file>)
FileName(<file>)
FileNameWithoutExtension(<file>)
FileText(<file>)
FileNameDateTime(<file>)
FileNameDate(<file>)
FileNameTime(<file>)

Common Output Options

/addRowNum
/append
/asText
/asCsv
/asHtml
/dateTimeFormat=<format>
/noHeaders
/toFiles

CSV Input Options

/inputSeparator=<separator>|Tab
/inputDateFormat=DMY|MDY
/firstRow=<first row>

CSV Output Options

/add=<header=value | function>[<separator>...]
/[output]Separator=<separator>|Tab
/qouteChar=<char>
/escapeChar=<char>

HTML Output Options

/noTemplate
/placeholder=<placeholder>
/template=<HTML template file>
/title=<title>

Code Generation Options

/table=<target database table | view | stored procedure> | <SQL template file>
/fmt=<format file>
/insertIdentity
/insertNulls
/singleLineSQL
/groupSize=<number of rows>
/keys=<field>[,<field>[,...]]
/mssql | /sqlce | /mysql | /oracle | /db2 | /nuodb | /pgsql | /sqlite

Exit Codes

Exit Code	Description
0	Success
1	Incomplete command line parameters
2	Wrong command line parameters
3	Exceptions occurred
4	Database server returns an error

Execute

gsqlcmd executes the specified SQL query or SQL script against the specified connection.

Use this mode to execute queries, export and import data.

You may omit the "execute" keyword. gsqlcmd uses this mode by default.

Prepare

gsqlcmd prepares the specified SQL query or SQL script to execute against the specified connection.

You may execute the result code in the execute mode or with another query tool.

Parse

gsqlcmd parses the specified SQL query or SQL script using the specified connection.

Use this mode to check how gsqlcmd parses queries into SQL commands.

Insert, Update, and Delete

gsqlcmd generates SQL statements for the specified CSV file.

Use the /table option to specify the target table, view, stored procedure, or SQL template file, and the /fmt= option to specify the format file with actual column names.

You may execute the generated SQL script using the execute mode.

The free version has a limit of 50 rows.

Merge

gsqlcmd generates insert and update SQL statements to merge data from the specified CSV file.

Use the /table option to specify the target table or view, and the /fmt= option to specify the format file with actual column names.

You may execute the generated SQL script using the execute mode.

The free version has a limit of 50 rows.

Fmt+

gsqlcmd generates configuration files for importing CSV files to Microsoft SQL Server using different techniques.

This mode is equivalent to a set of commands: Fmt, Create, Ace, Buck, and Ini.

If the format file exists, gsqlcmd uses it as a column name source. Otherwise, it creates a new format file.

Usually, you run gsqlcmd in this mode twice: the first for generating all files as is and the second after editing the format file.

Fmt

gsqlcmd generates format files for importing CSV files into Microsoft SQL Server using the OPENROWSET(BUCK...) function.

Also, gsqlcmd uses the format files as a column name sources in other modes.

Create

gsqlcmd generates CREATE TABLE statements to create tables to import CSV data.

Use the /table option to specify the target table and the /fmt= option to specify the format file with actual column names.

Ace

gsqlcmd generates OPENROWSET statements to import CSV data using the MICROSOFT.ACE.OLEDB.12.0 provider.

Use the /table option to specify the target table and the /fmt= option to specify the format file with actual column names.

You may use the OPENROWSET statements with the MICROSOFT.ACE.OLEDB.12.0 provider to import CSV data in the UTF-8 encoding.

Bulk

gsqlcmd generates OPENROWSET(BULK...) statements to import CSV data into Microsoft SQL Server.

Use the /table option to specify the target table and the /fmt= option to specify the format file with actual column names.

Ini

gsqlcmd generates the schema.ini file used to import CSV data into Microsoft Excel and databases using ODBC drivers.

Use the /fmt= option to specify the format file with actual column names.

createSqlCe35|createSqlCe40

gsqlcmd creates an SQL Compact database with a specified file name.

createSQLite3

gsqlcmd creates an SQLite database a specified file name.

LocalConfig | UserConfig | AppConfig

gsqlcmd starts gConnectionManager to edit named connections in configuration files:

LocalConfig - gsqlcmd.exe.config in the current directory.
UserConfig - gsqlcmd.exe.config in the %LocalAppData%\Gartle\gsqlcmd folder.
AppConfig - gsqlcmd.exe.config in the gsqlcmd install folder.

Connections

gsqlcmd lists connections from all configuration files described above.

Stamp

Use this mode in batch files to set environment variables:

for /F %%i in ('gsqlcmd.exe stamp') do set stamp=%%i

Register

gsqlcmd launches the Registration Wizard.

Help

gsqlcmd prints the extended command line help or starts this CHM help file for the chm parameter.

<Connection>

The parameter defines a connection to a database.

There are three options to specify the connection:

A connection name defined in gsqlcmd.exe.config (or gsqlcmd32.exe.config for gsqlcmd32.exe).
A connection string in the OLEDB, ODBC, or DSN format.
A .NET Framework data provider name and a connection string separated by a semicolon.

The first option is the best as you use short names of encrypted connection strings stored in the configuration files.

For example, gsqlcmd.exe.config can contain the following lines (in the unencrypted mode):

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <connectionStrings>
    <add name="AzureDemo" connectionString="Provider=SQLOLEDB;Data Source=.\SQLEXPRESS;Initial Catalog=AzureDemo;Integrated Security=SSPI"
      providerName="System.Data.OleDb" />
    <add name="rtd-mssql" connectionString="Data Source=.;Initial Catalog=RTD;Password=r#td_2014_abc!;User ID=rtd"
      providerName="System.Data.SqlClient" />
    <add name="rtd-sqlce" connectionString="Data Source=%LOCALAPPDATA%\Gartle\RealTimeToDB\rtd.sdf"
      providerName="System.Data.SqlServerCe.4.0" />
    <add name="rtd-mysql" connectionString="Server=localhost;Password=r#td_2014_abc!;User ID=rtd;Database=rtd"
      providerName="MySql.Data.MySqlClient" />
    <add name="rtd-ora" connectionString="Provider=OraOLEDB.Oracle;Password=r#td_2014_abc!;User ID=RTD;Data Source=Oracle/Orcl;PLSQLRSet=True"
      providerName="System.Data.OleDb" />
    <add name="rtd-db2" connectionString="Driver=IBM DB2 ODBC DRIVER;Hostname=DB2;Port=50000;Protocol=TCPIP;Database=SAMPLE;Pwd=r#td_2014_abc!;UID=RTD;LONGDATACOMPAT=1"
      providerName="System.Data.Odbc" />
    <add name="rtd-nuodb" connectionString="Server=localhost;Password=r#td_2014_abc!;User=RTD;Database=rtd"
      providerName="NuoDb.Data.Client" />
    <add name="rtd-pgsql" connectionString="Server=localhost;Password=r#td_2014_abc!;User ID=rtd;Database=rtd"
      providerName="Npgsql" />
  </connectionStrings>
</configuration>

This allows using names like AzureDemo, rtd-mssql, rtd-sqlce, and others as the command line connection parameter.

Use the LocalConfig, UserConfig, and AppConfig gsqlcmd modes to edit configurations files in a visual mode using gConnectionManager.

The second option allows specifying connection strings for OLE DB providers, ODBC drivers, and DSN files.

For example:

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

You may pass connection strings in environment variables. For example:

set connection=Provider=SQLOLEDB;Data Source=.\SQLEXPRESS;Initial Catalog=AzureDemo;Integrated Security=SSPI

gsqlcmd "%connection%" framework-install-en.sql

Use double quotes for such variables.

In the third connection option, use a .NET Framework provider name before the connection string and separate it with a semicolon.

For example:

gsqlcmd "MySql.Data.MySqlClient;Server=localhost;Password=r#td_2014_abc!;User ID=rtd;Database=rtd" framework-install-en.sql

<SQL Query> | <SQL File>

This positional parameter defines the inline SQL query or the SQL script file name of the execute mode.

For example:

gsqlcmd rtd-ora "SELECT * FROM SYS.ALL_USERS"

gsqlcmd rtd-ora rtd-setup-ora.sql

gsqlcmd tries to read the script from the file first. If the file does not exist, gsqlcmd uses the parameter as an inline query.

<CSV File>

This parameter defines the input CSV file.

<Output File>

This optional parameter defines an output file name.

gsqlcmd writes the output to the console by default.

gsqlcmd detects the output file format using the file extension:

*.txt - text format;
*.csv - CSV;
*.htm or *.html - HTML.

Use the /AsText, /AsCSV, and /AsHTML options to specify the output format explicitly.

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

Function	Meaning
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 hyphen	The 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 http://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

Function	Meaning
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 hyphen	The 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.

gConnectionManager allows editing connections in application configuration files.

You may use the gsqlcmd commands to start the gConnectionManager with a specific configuration file:

gsqlcmd localconfig | userconfig | appconfig

Also, you may open the desired configuration file using the File, Open menu item.

Here is a configuration file example:

Here is an example of selecting a connection provider:

Connection Manager - Selecting database connection provider

Here is an example of connecting to a database:

Connection Manager - Connecting to database

You may specify connections in the gsqlcmd.exe.config configuration file.

For example:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <connectionStrings>
    <add name="AzureDemo" connectionString="Provider=SQLOLEDB;Data Source=.\SQLEXPRESS;Initial Catalog=AzureDemo;Integrated Security=SSPI"
      providerName="System.Data.OleDb" />
    <add name="rtd-mssql" connectionString="Data Source=.;Initial Catalog=RTD;Password=r#td_2014_abc!;User ID=rtd"
      providerName="System.Data.SqlClient" />
    <add name="rtd-sqlce" connectionString="Data Source=%LOCALAPPDATA%\Gartle\RealTimeToDB\rtd.sdf"
      providerName="System.Data.SqlServerCe.4.0" />
    <add name="rtd-mysql" connectionString="Server=localhost;Password=r#td_2014_abc!;User ID=rtd;Database=rtd"
      providerName="MySql.Data.MySqlClient" />
    <add name="rtd-ora" connectionString="Provider=OraOLEDB.Oracle;Password=r#td_2014_abc!;User ID=RTD;Data Source=Oracle/Orcl;PLSQLRSet=True"
      providerName="System.Data.OleDb" />
    <add name="rtd-db2" connectionString="Driver=IBM DB2 ODBC DRIVER;Hostname=DB2;Port=50000;Protocol=TCPIP;Database=SAMPLE;Pwd=r#td_2014_abc!;UID=RTD;LONGDATACOMPAT=1"
      providerName="System.Data.Odbc" />
    <add name="rtd-nuodb" connectionString="Server=localhost;Password=r#td_2014_abc!;User=RTD;Database=rtd"
      providerName="NuoDb.Data.Client" />
    <add name="rtd-pgsql" connectionString="Server=localhost;Password=r#td_2014_abc!;User ID=rtd;Database=rtd"
      providerName="Npgsql" />
  </connectionStrings>
</configuration>

This allows using names like AzureDemo, rtd-mssql, or rtd-sqlce as the connection parameter.

Use LocalConfig, UserConfig, or AppConfig gsqlcmd modes to edit configurations files in visual mode using gConnectionManager.

Also, you may edit the configuration file using notepad.exe.

What executable, gsqlcmd.exe or gsqlcmd32.exe, should I use?: gsqlcmd uses database providers and drivers of the same bitness.
So, if you have both 64-bit and 32-bit providers installed, you may use any of executables.
But, if you have only the 32-bit providers installed on 64-bit Windows, use gsqlcmd32.exe.
gsqlcmd32.exe uses the gsqlcmd32.exe.config configuration file.
My script returns obscure errors. How to debug the script?: gsqlcmd allows executing DDL and DML scripts and supports the most useful extensions of native command line utilities.
You may try to execute the script using the native command line utility or your favorite IDE.
Also, you may specify the /trace option to get the text of each sent command and corresponding server reply.
If you think, that the standard script does not parsed correctly, please, send us the script text.

gsqlcmd requires registration to start a trial and to use it after the trial period.

To register, open Start Menu, All Programs, gsqlcmd, Register Product.

Selecting Edition

You may register the trial version for free; the product code is not required.

To register the gsqlcmd Personal or Enterprise edition fill in the product code received after purchasing.

gsqlcmd Registration - Select the edition

Licensee Data

Please fill in the registration form like this:

gsqlcmd Registration - Fill personal data

The Next button is enabled when all the required fields are filled.

Don't forget to check the required field about the personal data use.

Online Registration

This step allows you to check your email address. If the email is valid, click Next.

You may return to the previous step using the Back button.

After clicking the Next button, the gsqlcmd connects to the registration server.

If the connection is successful, the final step screen is shown.

If any error occurred during connection, you might try to register the product later or try to register the product by email.

You may set the registration for all users of the computer. This action requires administrator privileges.

Click Finish.

Registration by Email

If you choose the Register by email option on the Licensee Data step, the following screen is displayed:

gsqlcmd Registration - Check and start the registration email

The Next button starts the default email program, creates a registration email, and activates the next step.
Don't forget to send the email.

If starting of the email program has failed, you may create the registration email manually using the shown registration data.
Please carefully copy the To, Subject, and the Message fields.

The registration server sends the reply in a couple of seconds; but you may close the dialog box and open it again, in the same step.

Please copy the activation code from the received registration email and paste it into the Activation code field.

gsqlcmd Registration - Paste the activation code

The Next button is enabled when the activation code is valid.

Click Next to continue.

The wizard checks the registration data and confirms the registration.

You may set the registration for all users of the computer. This action requires administrator privileges.

Click Finish.

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

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

gsqlcmd

Getting Started

Change History

Version 4.3, May 31, 2017

Version 4.2, May 11, 2017

Version 4.1, February 3, 2017

Version 4.0.3, January 25, 2017

Version 4.0, January 17, 2017

Edition Comparison

Install and Uninstall

System Requirements

End-User License Agreement

Trial Version Limitations

Using gsqlcmd

Database Connections

Executing SQL Scripts

Exporting Database Data

Using Variables in SQL Scripts

Internal Script Commands

Transaction Modes

Trace Mode

Importing CSV Data into Databases

Creating Format Files

Creating Database Tables

Importing CSV Data into Microsoft SQL Server using BCP

Creating and Using OPENROWSET(BUCK...) Statements

Creating SCHEMA.INI Sections for Using with ODBC

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

Creating INSERT Statements for Importing CSV Data

Creating INSERT and UPDATE Statements for Merging CSV Data

Command Line

Command Line Modes

<Connection> Format

Query Execution Options

Functions for /set and /add parameters

Common Output Options

CSV Input Options

CSV Output Options

HTML Output Options

Code Generation Options

Exit Codes

Command Line Modes

Execute

Prepare

Parse

Insert, Update, and Delete

Merge

Fmt+

Fmt

Create

Ace

Bulk

Ini

createSqlCe35|createSqlCe40

createSQLite3

LocalConfig | UserConfig | AppConfig

Connections

Stamp

Register

Help

Positional Parameters

<Connection>

<SQL Query> | <SQL File>

<CSV File>

<Output File>

Command Line Options

Query Execution Options

/commandTimeout=<seconds>

/connectionTimeout=<seconds>

/noTransaction

/inputCodePage=<codepage>

/outputCodePage=<codepage>

/outputCulture=<name>

/noBOM

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

Functions to use with /set and /add options

/fromFiles=<mask>

/trace

Common Output Options

/addRowNum