Importing Data with gsqlcmd

Importing Data with gsqlcmd

Contents:

Import Modes

Suppose we have data.csv with the following content:

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

We have to import data into a table with the following structure:

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

The first way to import data includes two commands, make and exec:

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

gsqlcmd exec db merge.sql

The make command produces the following result in the 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 executes the generated SQL commands.

The second way is to use a single import command instead:

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

The import command has the same parameters as the make command except for the output file for SQL commands.

Insert, Update, Merge, and Delete

The make and import commands have the commands option with the following values: insert, update, merge, and delete

You can test 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. Below are 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

The command produces SQL commands including 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 sample above imports data with the id column values.

The used command imports id and name column values as the column names are the same in the source file and the target table.

Suppose we have another file, data2.csv, with the following content:

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

In the first step, create a select SQL command for the source file:

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

Its content is below:

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

Change it to the following:

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

And use the command:

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

This command uses the input file with the .sql extension. So, gsqlcmd uses its query instead.

Note that the .sql extension is important in this case. Otherwise, gsqlcmd uses the file as a data source.

gsqlcmd has a built-in SELECT parser. It supports the SELECT, FROM, WHERE and ORDER BY clauses.

So, you can change column sets and column names.

However, gsqlcmd does not support JOIN, GROUP BY, and other SQL SELECT features.

If you need to modify the source file, you can first use the ODBC or OLE DB text drivers to produce the required output.

For example, you can create the following query in the 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. So, gsqlcmd creates an ODBC connection string for the text file and executes select-data3.sql.

In the gsqlcmd Enterprise edition, you can use queries with the second connection.

So, you can use a single command like this:

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

Customizing JOIN Conditions

The samples above import data using id column values as primary keys.

If you need to use another field instead, use the /keys option.

For example, we can omit 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 has 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 one discussed above.

However, it can require specific web request options like referrer and specific parser options to extract data.

Here is 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 result, and gsqlcmd imports it into the table "data".

The table has the same column names as the source JSON. So, the command is simple.

Here is a 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

It uses the /referrer web request option and the /rootPath and /skippedNodes parser options.

Also, it uses 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 a lot of samples in the "Examples\Downloading and importing into DB" folder of the gsqlcmd download package.