INSERT keyword

Inserts data into a database table.

Syntax

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:

  unit	description
  us	microseconds
  s	seconds
  m	minutes
  h	hours
  d	days

Examples

Inserting all columns

INSERT INTO trades

VALUES(

'2021-10-05T11:31:35.878Z',

'AAPL',

255,

123.33,

'B');

Bulk inserts

INSERT INTO trades

VALUES

('2021-10-05T11:31:35.878Z', 'AAPL', 245, 123.4, 'C'),

('2021-10-05T12:31:35.878Z', 'AAPL', 245, 123.3, 'C'),

('2021-10-05T13:31:35.878Z', 'AAPL', 250, 123.1, 'C'),

('2021-10-05T14:31:35.878Z', 'AAPL', 250, 123.0, 'C');

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

• Using the lag and batch size parameters during INSERT AS SELECT statements is a convenient strategy to load and order large datasets from CSV in bulk. This strategy along with an example workflow is described in the importing data guide.

• More details on ingesting out-of-order data with context on lag and uncommitted record count see the guide for configuring commit lag of out-of-order data

• Hints and an example workflow using INSERT AS SELECT for bulk CSV import of out-of-order data can be found on the importing data via CSV documentation.