> ## Documentation Index
> Fetch the complete documentation index at: https://private-7c7dfe99-mintlify-8c05c8a2.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# Inserting and dumping SQL data in ClickHouse

> Page describing how to transfer data between other databases and ClickHouse using SQL dumps.

ClickHouse can be easily integrated into OLTP database infrastructures in many ways. One way is to transfer data between other databases and ClickHouse using SQL dumps.

<h2 id="creating-sql-dumps">
  Creating SQL dumps
</h2>

Data can be dumped in SQL format using [SQLInsert](/reference/formats/SQLInsert). ClickHouse will write data in `INSERT INTO <table name> VALUES(...` form and use [`output_format_sql_insert_table_name`](/reference/settings/formats#output_format_sql_insert_table_name) settings option as a table name:

```sql theme={null}
SET output_format_sql_insert_table_name = 'some_table';
SELECT * FROM some_data
INTO OUTFILE 'dump.sql'
FORMAT SQLInsert
```

Column names can be omitted by disabling [`output_format_sql_insert_include_column_names`](/reference/settings/formats#output_format_sql_insert_include_column_names) option:

```sql theme={null}
SET output_format_sql_insert_include_column_names = 0
```

Now we can feed [dump.sql](/assets/dump.sql) file to another OLTP database:

```bash theme={null}
mysql some_db < dump.sql
```

We assume that the `some_table` table exists in the `some_db` MySQL database.

Some DBMSs might have limits on how much values can be processes within a single batch. By default, ClickHouse will create 65k values batches, but that can be changed with the [`output_format_sql_insert_max_batch_size`](/reference/settings/formats#output_format_sql_insert_max_batch_size) option:

```sql theme={null}
SET output_format_sql_insert_max_batch_size = 1000;
```

<h3 id="exporting-a-set-of-values">
  Exporting a set of values
</h3>

ClickHouse has [Values](/reference/formats/Values) format, which is similar to SQLInsert, but omits an `INSERT INTO table VALUES` part and returns only a set of values:

```sql theme={null}
SELECT * FROM some_data LIMIT 3 FORMAT Values
```

```response theme={null}
('Bangor_City_Forest','2015-07-01',34),('Alireza_Afzal','2017-02-01',24),('Akhaura-Laksam-Chittagong_Line','2015-09-01',30)
```

<h2 id="inserting-data-from-sql-dumps">
  Inserting data from SQL dumps
</h2>

To read SQL dumps, [MySQLDump](/reference/formats/MySQLDump) is used:

```sql theme={null}
SELECT *
FROM file('dump.sql', MySQLDump)
LIMIT 5
```

```response theme={null}
┌─path───────────────────────────┬──────month─┬─hits─┐
│ Bangor_City_Forest             │ 2015-07-01 │   34 │
│ Alireza_Afzal                  │ 2017-02-01 │   24 │
│ Akhaura-Laksam-Chittagong_Line │ 2015-09-01 │   30 │
│ 1973_National_500              │ 2017-10-01 │   80 │
│ Attachment                     │ 2017-09-01 │ 1356 │
└────────────────────────────────┴────────────┴──────┘
```

By default, ClickHouse will skip unknown columns (controlled by [input\_format\_skip\_unknown\_fields](/reference/settings/formats#input_format_skip_unknown_fields) option) and process data for the first found table in a dump (in case multiple tables were dumped to a single file). DDL statements will be skipped. To load data from MySQL dump into a table ([mysql.sql](/assets/mysql.sql) file):

```sql theme={null}
INSERT INTO some_data
FROM INFILE 'mysql.sql' FORMAT MySQLDump
```

We can also create a table automatically from the MySQL dump file:

```sql theme={null}
CREATE TABLE table_from_mysql
ENGINE = MergeTree
ORDER BY tuple() AS
SELECT *
FROM file('mysql.sql', MySQLDump)
```

Here we've created a table named `table_from_mysql` based on a structure that ClickHouse automatically inferred.  ClickHouse either detects types based on data or uses DDL when available:

```sql theme={null}
DESCRIBE TABLE table_from_mysql;
```

```response theme={null}
┌─name──┬─type─────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ path  │ Nullable(String) │              │                    │         │                  │                │
│ month │ Nullable(Date32) │              │                    │         │                  │                │
│ hits  │ Nullable(UInt32) │              │                    │         │                  │                │
└───────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
```

<h2 id="other-formats">
  Other formats
</h2>

ClickHouse introduces support for many formats, both text, and binary, to cover various scenarios and platforms. Explore more formats and ways to work with them in the following articles:

* [CSV and TSV formats](/guides/clickhouse/data-formats/csv-tsv)
* [Parquet](/guides/clickhouse/data-formats/parquet)
* [JSON formats](/guides/clickhouse/data-formats/json/intro)
* [Regex and templates](/guides/clickhouse/data-formats/templates-regex)
* [Native and binary formats](/guides/clickhouse/data-formats/binary)
* **SQL formats**

And also check [clickhouse-local](https://clickhouse.com/blog/extracting-converting-querying-local-files-with-sql-clickhouse-local) - a portable full-featured tool to work on local/remote files without the need for ClickHouse server.