Creating Tables with gsqlcmd

The gsqlcmd tool allows you to generate CREATE TABLE statements easily.

For example, consider the following content in aapl.csv:

symbol;time;open;high;low;close;volume
AAPL;2019-04-09 13:30:00;200.3200;200.4500;199.68;200.3800;1332001
AAPL;2019-04-09 13:31:00;200.3726;200.8850;200.32;200.6920;351343

You can create a table to store this data using the make-create mode:

gsqlcmd make-create aapl.csv create-yahoo1m.sql /table=dbo.yahoo1m /mssql

Use the /table option to specify the table name.
Use the /serverType option to define the target database platform. Options include /mssql, /mysql, /oracle, /pgsql, /snowflake, or /sqlite.

The generated create-yahoo1m.sql will look like this:

CREATE TABLE [dbo].[yahoo1m] (
    [ID] int IDENTITY(1,1) NOT NULL,
    [symbol] nvarchar(255) NULL,
    [time] datetime NULL,
    [open] float NULL,
    [high] float NULL,
    [low] float NULL,
    [close] float NULL,
    [volume] int NULL,
  CONSTRAINT [PK_yahoo1m_dbo] PRIMARY KEY ([ID])
);
GO
print N'Table [dbo].[yahoo1m] created';
GO

By default, gsqlcmd adds an ID field as the IDENTITY field if the source data lacks a column with unique integer values.

You can specify primary key fields using the /keys option. For example:

gsqlcmd make-create aapl.csv create-yahoo1m.sql /table=dbo.yahoo1m /mssql /keys=symbol,time

The resulting create-yahoo1m.sql will be:

CREATE TABLE [dbo].[yahoo1m] (
    [symbol] nvarchar(255) NOT NULL,
    [time] datetime NOT NULL,
    [open] float NULL,
    [high] float NULL,
    [low] float NULL,
    [close] float NULL,
    [volume] int NULL,
  CONSTRAINT [PK_yahoo1m_dbo] PRIMARY KEY (
    [symbol],
    [time]
  )
);
GO
print N'Table [dbo].[yahoo1m] created';
GO

gsqlcmd automatically detects column data types, but you should verify and adjust them as needed. Always review nvarchar(255) fields; for instance, varchar(25) may be more appropriate in some cases.

If you created a format file to rename column names, use it with the /formatFile option. For example:

gsqlcmd make-create aapl.csv create-yahoo1m.sql /table=dbo.yahoo1m /mssql /keys=symbol,time /formatFile=yahoo1m.fmt