Inserting Data (INSERT INTO)

Basic Syntax:

INSERT INTO [db.]table [(c1, c2, c3)] VALUES (v11, v12, v13), (v21, v22, v23), ...

For columns that exist in the table structure but are not present in the insertion list, they will be filled in the following manner:

• If a DEFAULT expression exists, the value is filled based on the calculation of the DEFAULT expression.
• If no DEFAULT expression is defined, zero or an empty string is filled in.

Writing Using the Result of SELECT

Basic Syntax:

INSERT INTO [db.]table [(c1, c2, c3)] SELECT ...

The correspondence between the columns to be written and those in the SELECT statement is based on position; their names in the SELECT expression and INSERT can be different. Type conversion is required for them to correspond.

Expressions like now(), 1 + 2, etc. are not allowed in data formats other than VALUES. The VALUES format allows you to use these expressions to a limited extent, but it is not recommended, as executing such expressions is inefficient.

Performance Considerations

When performing an INSERT, some processing will be carried out on the data being written, such as sorting by primary key, partitioning by month, etc. If your inserted data includes a mix of multiple months, the INSERT performance will be significantly reduced. To avoid this, the following methods are typically employed:

• Always write data in the largest possible batches, such as writing 100,000 rows at a time.
• Pre-group data before writing it to ClickHouse.

Performance will not degrade in the following cases:

• Data is always written in real-time.
• The data being written is already sorted by time.