Creating Tables with gsqlcmd

gsqlcmd allows generating CREATE TABLE statements.

For example, aapl.csv has the following content:

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 generate a code to create a table to store these data using the make-create mode:

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

Use the /table option to specify a table name.

Use the /serverType option to specify the target database platform.

You can use the option as /mssql, /sqlce, /mysql, /oracle, /db2, /nuodb, /pgsql, /snowflake, or /sqlite.

create-yahoo1m.sql has the following content:

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 the ID field used as the IDENTITY field if the source data does not contain a column with unique integer values.

You can use the /keys option to specify primary key fields.

For example:

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

create-yahoo1m.sql has the following content:

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 detects column data types. However, you have to check and modify them if required.

Always check nvarchar(255) fields. For example, in the code above, varchar(25) is a better choice.

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