Importing Data with gsqlcmd

Importing Data with gsqlcmd

Contents:

Import Modes

Let's say we have a file named data.csv with the following content:

id;"name"
1;"Customer C1"
2;"Customer C2"
3;"Customer C3"

We need to import this data into a table structured like this:

CREATE TABLE [data] (
    [id] integer PRIMARY KEY NOT NULL,
    [name] varchar(50) NULL
);

The first method to import data involves two commands: make and exec:

gsqlcmd make db data.csv merge.sql /table=data /merge
gsqlcmd exec db merge.sql

The make command generates the following SQL in merge.sql:

UPDATE [data] SET [name] = 'Customer C1' WHERE [id] = 1;
UPDATE [data] SET [name] = 'Customer C2' WHERE [id] = 2;
UPDATE [data] SET [name] = 'Customer C3' WHERE [id] = 3;
INSERT INTO [data] ([name]) SELECT s.[name] FROM ( SELECT s.[id], s.[name] FROM (SELECT 1 AS [id], 'Customer C1' AS [name]) s LEFT OUTER JOIN [data] t ON t.[id] = s.[id] WHERE t.[id] IS NULL) s;
INSERT INTO [data] ([name]) SELECT s.[name] FROM ( SELECT s.[id], s.[name] FROM (SELECT 2 AS [id], 'Customer C2' AS [name]) s LEFT OUTER JOIN [data] t ON t.[id] = s.[id] WHERE t.[id] IS NULL) s;
INSERT INTO [data] ([name]) SELECT s.[name] FROM ( SELECT s.[id], s.[name] FROM (SELECT 3 AS [id], 'Customer C3' AS [name]) s LEFT OUTER JOIN [data] t ON t.[id] = s.[id] WHERE t.[id] IS NULL) s;
-- print Processed 3 merge records

The exec command runs the generated SQL commands.

Alternatively, you can use a single import command:

gsqlcmd import db data.csv /table=data /merge

The import command accepts the same parameters as make, but it does not generate an output SQL file.

Insert, Update, Merge, and Delete

Both the make and import commands support the commands option with these values: insert, update, merge, and delete.

You can test the generated SQL commands using a batch file like this:

gsqlcmd make db data.csv test-merge.sql  /table=data /merge
gsqlcmd make db data.csv test-insert.sql /table=data /insert
gsqlcmd make db data.csv test-update.sql /table=data /update
gsqlcmd make db data.csv test-delete.sql /table=data /delete

The merge result is shown above. Here are the other results:

INSERT INTO [data] ([name]) VALUES ('Customer C1');
INSERT INTO [data] ([name]) VALUES ('Customer C2');
INSERT INTO [data] ([name]) VALUES ('Customer C3');
-- print Processed 3 insert records

UPDATE [data] SET [name] = 'Customer C1' WHERE [id] = 1;
UPDATE [data] SET [name] = 'Customer C2' WHERE [id] = 2;
UPDATE [data] SET [name] = 'Customer C3' WHERE [id] = 3;
-- print Processed 3 update records

DELETE FROM [data] WHERE [id] = 1;
DELETE FROM [data] WHERE [id] = 2;
DELETE FROM [data] WHERE [id] = 3;
-- print Processed 3 delete records

Inserting IDENTITY Values into SQL Server Tables

To import identity column values into SQL Server databases, use the /insertIdentity option.

For example:

gsqlcmd make db1 data.csv test-insert.sql /table=s61.table12 /insert /insertIdentity

This command generates SQL commands that include SET IDENTITY_INSERT:

SET IDENTITY_INSERT [s61].[table12] ON;
INSERT INTO [s61].[table12] ([id], [name]) VALUES (1, N'Customer C1');
INSERT INTO [s61].[table12] ([id], [name]) VALUES (2, N'Customer C2');
INSERT INTO [s61].[table12] ([id], [name]) VALUES (3, N'Customer C3');
SET IDENTITY_INSERT [s61].[table12] OFF;
GO
print 'Processed 3 insert records';
GO

Customizing Column Sets and Column Names

The previous example imports data using the id column values.

The command imports both id and name column values since the column names match in the source file and the target table.

Now, consider another file, data2.csv, with this content:

company_id;"company_name"
1;"Customer C1"
2;"Customer C2"
3;"Customer C3"

First, create a select SQL command for the source file:

gsqlcmd make-select data2.csv select-data2.sql

The content will be:

SELECT
    t.company_id
    , t.company_name
FROM
    [data2.csv] t

Modify it to:

SELECT
    t.company_id AS id
    , t.company_name AS name
FROM
    [data2.csv] t

Then run the command:

gsqlcmd import db select-data2.sql /table=data /merge

This command uses the input file with the .sql extension, allowing gsqlcmd to execute the query instead.

Note that the .sql extension is crucial here; otherwise, gsqlcmd treats the file as a data source.

gsqlcmd has a built-in SELECT parser that supports the SELECT, FROM, WHERE, and ORDER BY clauses, allowing you to modify column sets and names. However, it does not support JOIN, GROUP BY, or other SQL SELECT features.

If you need to modify the source file, consider using ODBC or OLE DB text drivers to produce the required output.

For example, create the following query in select-data3.sql:

SELECT
    t.company_id AS id
    , t.company_name AS name
FROM
    [data2.csv] t
WHERE
    t.company_name LIKE 'Customer %'
ORDER BY
    t.company_name

Then use the exec mode to convert data:

gsqlcmd exec data2.csv select-data3.sql data3.csv

This command uses data2.csv as a connection parameter, allowing gsqlcmd to create an ODBC connection string for the text file and execute select-data3.sql.

In the gsqlcmd Enterprise edition, you can use queries with the second connection, enabling a single command like this:

gsqlcmd import db data2.csv::select-data3.sql /table=data /merge

Customizing JOIN Conditions

The previous examples import data using id column values as primary keys.

If you want to use a different field, utilize the /keys option.

For instance, you can skip the id field and use the name field in the merge command:

gsqlcmd make db "SELECT name FROM data.csv" /table=data /merge /keys=name

The command generates the following output:

INSERT INTO [data] ([name]) SELECT s.[name] FROM (SELECT 'Customer C1' AS [name]) s LEFT OUTER JOIN [data] t ON t.[name] = s.[name] WHERE t.[name] IS NULL;
INSERT INTO [data] ([name]) SELECT s.[name] FROM (SELECT 'Customer C2' AS [name]) s LEFT OUTER JOIN [data] t ON t.[name] = s.[name] WHERE t.[name] IS NULL;
INSERT INTO [data] ([name]) SELECT s.[name] FROM (SELECT 'Customer C3' AS [name]) s LEFT OUTER JOIN [data] t ON t.[name] = s.[name] WHERE t.[name] IS NULL;
-- print Processed 3 merge records

Importing Data from the Web

Importing data from the web is similar to the methods discussed above but may require specific web request options like referrer and certain parser options to extract data.

Here's a sample batch file that imports stock quotes from Yahoo Finance:

set url="https://query1.finance.yahoo.com/v7/finance/quote?symbols={symbol}"

gsqlcmd import db %url% /table=data /merge /taskfile=symbols.txt /echoURL

Yahoo Finance returns a JSON response, and gsqlcmd imports it into the "data" table, which has the same column names as the source JSON. Thus, the command is straightforward.

Here’s another sample batch file that imports historical stock prices from MSN Money:

gsqlcmd import db select-from-url.sql /table=data /merge /taskfile=symbols.txt /echoURL ^
        /referrer=https://www.msn.com/en-us/money/ ^
        /rootPath=Chart.Series /skippedNodes=IsStitched

This command uses the /referrer web request option along with the /rootPath and /skippedNodes parser options.

It also employs a select query to rename source JSON column names and add the symbol column:

SELECT
    '{symbol}' AS [symbol]
    , T AS [date]
    , Op AS [open]
    , Hp AS [high]
    , Lp AS [low]
    , P AS [close]
    , V AS volume
FROM
    https://finance-services.msn.com/Market.svc/ChartAndQuotes?symbols={symbol}&chartType=1y&isEOD=True&isCS=true&isVol=true

You can find numerous examples in the "Examples\Downloading and importing into DB" folder of the gsqlcmd download package.

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