INSERT keyword

Inserts data into a database table.

Syntax#

Flow chart showing the syntax of the INSERT keyword

Parameters#

Two parameters may be provided to optimize INSERT AS SELECT queries when inserting out-of-order records into an ordered dataset:

  • batch expects a batchCount (integer) value how many records to process at any one time

  • commitLag expects a lagAmount with a modifier to specify the unit of time for the value (i.e. 20s for 20 seconds). The following table describes the units that may be passed:

    unitdescription
    usmicroseconds
    sseconds
    mminutes
    hhours
    ddays

Examples#

Inserting all columns
INSERT INTO trades
VALUES(
to_timestamp('2019-10-17T00:00:00', 'yyyy-MM-ddTHH:mm:ss'),
'AAPL',
255,
123.33,
'B');
Specifying schema
INSERT INTO trades (timestamp, symbol, quantity, price, side)
VALUES(
to_timestamp('2019-10-17T00:00:00', 'yyyy-MM-ddTHH:mm:ss'),
'AAPL',
255,
123.33,
'B');
note

Columns can be omitted during INSERT in which case value will be NULL

Inserting only specific columns
INSERT INTO trades (timestamp, symbol, price)
VALUES(to_timestamp('2019-10-17T00:00:00', 'yyyy-MM-ddTHH:mm:ss'),'AAPL','B');

Inserting query results#

This method allows you to insert as many rows as your query returns at once.

Insert as select
INSERT INTO confirmed_trades
SELECT timestamp, instrument, quantity, price, side
FROM unconfirmed_trades
WHERE trade_id = '47219345234';

Inserting out-of-order data into an ordered dataset may be optimized using batch and commitLag parameters:

Insert as select with lag and batch size
INSERT batch 100000 commitLag 180s INTO trades
SELECT ts, instrument, quantity, price
FROM unordered_trades
info