INSERT keyword

INSERT ingests selected data into a database table.

Syntax#

Inserting values directly or using sub-queries:

Flow chart showing the syntax of the INSERT keyword

Inserting using sub-query alias:

Flow chart showing the syntax of the WITH AS INSERT keyword

Description#

note

If the target partition is attached by a symbolic link, the partition is read-only. INSERT operation on a read-only partition triggers a critical-level log in the server, and the insert is a no-op.

Inserting values directly or using sub-queries:

  • VALUE: Directly defines the values to be inserted.
  • SELECT: Inserts values based on the result of a SELECT query

Setting sub-query alias:

  • WITH AS: Inserts values based on a sub-query, to which an alias is given by using WITH.

Parameter:

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

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 the 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';

Using the WITH keyword to set up an alias for a SELECT sub-query:

Insert with sub-query
WITH confirmed_id AS (
SELECT * FROM unconfirmed_trades
WHERE trade_id = '47219345234'
)
INSERT INTO confirmed_trades
SELECT * FROM confirmed_id;

โญ Something missing? Page not helpful? Please suggest an edit on GitHub.