INSERT INTO Statement

This functionality is available in the [command-line client](/docs/interfaces/client) and [clickhouse-local](/docs/operations/utilities/clickhouse-local).

**Examples**

### Single file with FROM INFILE[​](#single-file-with-from-infile "Direct link to Single file with FROM INFILE")

Execute the following queries using [command-line client](/docs/interfaces/client):

```
echo 1,A > input.csv ; echo 2,B >> input.csv
clickhouse-client --query="CREATE TABLE table_from_file (id UInt32, text String) ENGINE=MergeTree() ORDER BY id;"
clickhouse-client --query="INSERT INTO table_from_file FROM INFILE 'input.csv' FORMAT CSV;"
clickhouse-client --query="SELECT * FROM table_from_file FORMAT PrettyCompact;"
```

Result:

```
┌─id─┬─text─┐
│  1 │ A    │
│  2 │ B    │
└────┴──────┘
```

### Multiple files with FROM INFILE using globs[​](#multiple-files-with-from-infile-using-globs "Direct link to Multiple files with FROM INFILE using globs")

This example is very similar to the previous one but inserts are performed from multiple files using `FROM INFILE 'input_*.csv`.

```
echo 1,A > input_1.csv ; echo 2,B > input_2.csv
clickhouse-client --query="CREATE TABLE infile_globs (id UInt32, text String) ENGINE=MergeTree() ORDER BY id;"
clickhouse-client --query="INSERT INTO infile_globs FROM INFILE 'input_*.csv' FORMAT CSV;"
clickhouse-client --query="SELECT * FROM infile_globs FORMAT PrettyCompact;"
```

Tip

In addition to selecting multiple files with `*`, you can use ranges (`{1,2}` or `{1..9}`) and other [glob substitutions](/docs/sql-reference/table-functions/file#globs-in-path). These three all would work with the example above:

```
INSERT INTO infile_globs FROM INFILE 'input_*.csv' FORMAT CSV;
INSERT INTO infile_globs FROM INFILE 'input_{1,2}.csv' FORMAT CSV;
INSERT INTO infile_globs FROM INFILE 'input_?.csv' FORMAT CSV;
```

## Inserting using a Table Function[​](#inserting-using-a-table-function "Direct link to Inserting using a Table Function")

Data can be inserted into tables referenced by [table functions](/docs/sql-reference/table-functions).

**Syntax**

```
INSERT INTO [TABLE] FUNCTION table_func ...
```

**Example**

The [remote](/docs/sql-reference/table-functions/remote) table function is used in the following queries:

```
CREATE TABLE simple_table (id UInt32, text String) ENGINE=MergeTree() ORDER BY id;
INSERT INTO TABLE FUNCTION remote('localhost', default.simple_table)
    VALUES (100, 'inserted via remote()');
SELECT * FROM simple_table;
```

Result:

```
┌──id─┬─text──────────────────┐
│ 100 │ inserted via remote() │
└─────┴───────────────────────┘
```

## Inserting into ClickHouse Cloud[​](#inserting-into-clickhouse-cloud "Direct link to Inserting into ClickHouse Cloud")

By default, services on ClickHouse Cloud provide multiple replicas for high availability. When you connect to a service, a connection is established to one of these replicas.

After an `INSERT` succeeds, data is written to the underlying storage. However, it may take some time for replicas to receive these updates. Therefore, if you use a different connection that executes a `SELECT` query on one of these other replicas, the updated data may not yet be reflected.

It is possible to use the `select_sequential_consistency` to force the replica to receive the latest updates. Here is an example of a `SELECT` query using this setting:

```
SELECT .... SETTINGS select_sequential_consistency = 1;
```

Note that using `select_sequential_consistency` will increase the load on ClickHouse Keeper (used by ClickHouse Cloud internally) and may result in slower performance depending on the load on the service. We recommend against enabling this setting unless necessary. The recommended approach is to execute read/writes in the same session or to use a client driver that uses the native protocol (and thus supports sticky connections).

## Inserting into a replicated setup[​](#inserting-into-a-replicated-setup "Direct link to Inserting into a replicated setup")

In a replicated setup, data will be visible on other replicas after it has been replicated. Data begins being replicated (downloaded on other replicas) immediately after an `INSERT`. This differs from ClickHouse Cloud, where data is immediately written to shared storage and replicas subscribe to metadata changes.

Note that for replicated setups, `INSERTs` can sometimes take a considerable amount of time (in the order of one second) as it requires committing to ClickHouse Keeper for distributed consensus. Using S3 for storage also adds additional latency.

## Performance Considerations[​](#performance-considerations "Direct link to Performance Considerations")

`INSERT` sorts the input data by primary key and splits them into partitions by a partition key. If you insert data into several partitions at once, it can significantly reduce the performance of the `INSERT` query. To avoid this:

- Add data in fairly large batches, such as 100,000 rows at a time.
- Group data by a partition key before uploading it to ClickHouse.

Performance will not decrease if:

- Data is added in real time.
- You upload data that is usually sorted by time.

### Asynchronous inserts[​](#asynchronous-inserts "Direct link to Asynchronous inserts")

It is possible to asynchronously insert data in small but frequent inserts. The data from such insertions is combined into batches and then safely inserted into a table. To use asynchronous inserts, enable the [`async_insert`](/docs/operations/settings/settings#async_insert) setting.

Using `async_insert` or the [`Buffer` table engine](/docs/engines/table-engines/special/buffer) results in additional buffering.

### Large or long-running inserts[​](#large-or-long-running-inserts "Direct link to Large or long-running inserts")

When you are inserting large amounts of data, ClickHouse will optimize write performance through a process called "squashing". Small blocks of inserted data in memory are merged and squashed into larger blocks before being written to disk. Squashing reduces the overhead associated with each write operation. In this process, inserted data will be available to query after ClickHouse completes writing each [`max_insert_block_size`](/docs/operations/settings/settings#max_insert_block_size) rows.

**See Also**

- [async\_insert](/docs/operations/settings/settings#async_insert)
- [wait\_for\_async\_insert](/docs/operations/settings/settings#wait_for_async_insert)
- [wait\_for\_async\_insert\_timeout](/docs/operations/settings/settings#wait_for_async_insert_timeout)
- [async\_insert\_max\_data\_size](/docs/operations/settings/settings#async_insert_max_data_size)
- [async\_insert\_busy\_timeout\_ms](/docs/operations/settings/settings#async_insert_busy_timeout_max_ms)
- [async\_insert\_stale\_timeout\_ms](/docs/operations/settings/settings#async_insert_max_data_size)

- [Constraints](#constraints)- [Data Type Validation](#data-type-validation)- [Inserting the Results of SELECT](#inserting-the-results-of-select)- [Inserting Data from a File](#inserting-data-from-a-file)
        - [Single file with FROM INFILE](#single-file-with-from-infile)- [Multiple files with FROM INFILE using globs](#multiple-files-with-from-infile-using-globs)- [Inserting using a Table Function](#inserting-using-a-table-function)- [Inserting into ClickHouse Cloud](#inserting-into-clickhouse-cloud)- [Inserting into a replicated setup](#inserting-into-a-replicated-setup)- [Performance Considerations](#performance-considerations)
                - [Asynchronous inserts](#asynchronous-inserts)- [Large or long-running inserts](#large-or-long-running-inserts)

Was this page helpful?