Getting Started
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
Change History
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:
- Register mode
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
Edition Comparison
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 | ✓ |
Install and Uninstall
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.
Register the application to start a trial period.
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.
System Requirements
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
End-User License Agreement
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:
- 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. - 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. - 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.
- Use of Examples: You may install, access, modify and use Software examples for your private or company-internal purposes.
- 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
- 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
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
Trial Version Limitations
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.
See also
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:
- <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
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 |
Command Line Modes
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.
Positional Parameters
<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.
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
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
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:

Here is an example of connecting to a database:

Configuration File
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.
Frequently Asked Questions
- 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.
Product Registration
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.

Licensee Data
Please fill in the registration form like this:

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:

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.

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.