This topic describes the syntax of an INSERT INTO statement that is used to insert data into a table in ApsaraDB for ClickHouse.

Syntax

The following code shows the syntax of an INSERT INTO statement:

INSERT INTO [db.]table [(c1, c2, c3)] VALUES (v11, v12, v13), (v21, v22, v23), ...
For a field that is defined in the table schema but not specified in an INSERT INTO statement, the system fills in the field based on the following rules:
  • If an expression is defined for the default values of the field, the system calculates the default values by using this expression and then inserts the default values into the field.
  • If no expression is defined for the default values of the field, the system inserts 0 or empty strings into the field.

Use the SELECT clause in an INSERT INTO statement

The following code shows the syntax:

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

Data in the fields that are queried in the SELECT clause are inserted into the fields that are specified in the INSERT INTO statement in strict order. The names of the two sets of fields do not have to be the same. The system converts the data types of the fields as needed.

Except for the VALUES type, all other data types do not support expressions such as now() and 1 + 2. The VALUES type allows you to use such expressions. However, we recommend that you do not use these expressions because the execution of these expressions is inefficient.

Suggestions on ensuring high performance of INSERT INTO statements

When the system executes an INSERT INTO statement, the system processes the data to be inserted. For example, the system sorts the data by the primary key or partitions the data by month. If the data to be inserted spans multiple months, the performance of an INSERT INTO statement is significantly impaired. To prevent this issue, you can take the following measures:
  • Always write a large volume of data at a time. For example, you can write 100,000 rows of data each time.
  • Group data before you write the data to an ApsaraDB for ClickHouse cluster.
In the following scenarios, the performance of INSERT INTO statements is not affected:
  • Data is always written in real time.
  • The data to be written is already sorted by time.