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

> The `Merge` engine (not to be confused with `MergeTree`) does not store data itself, but allows reading from any number of other tables simultaneously.

# Merge table engine

The `Merge` engine (not to be confused with `MergeTree`) does not store data itself, but allows reading from any number of other tables simultaneously.

Reading is automatically parallelized. Writing to a table is not supported. When reading, the indexes of tables that are actually being read are used, if they exist.

<h2 id="creating-a-table">
  Creating a table
</h2>

```sql theme={null}
CREATE TABLE ... Engine=Merge(db_name, tables_regexp)
```

<h2 id="engine-parameters">
  Engine parameters
</h2>

<h3 id="db_name">
  `db_name`
</h3>

`db_name` — Possible values:

* database name,
* constant expression that returns a string with a database name, for example, `currentDatabase()`,
* `REGEXP(expression)`, where `expression` is a regular expression to match the DB names.

<h3 id="tables_regexp">
  `tables_regexp`
</h3>

`tables_regexp` — A regular expression to match the table names in the specified DB or DBs.

Regular expressions — [re2](https://github.com/google/re2) (supports a subset of PCRE), case-sensitive.
See the notes about escaping symbols in regular expressions in the "match" section.

<h2 id="usage">
  Usage
</h2>

When selecting tables to read, the `Merge` table itself is not selected, even if it matches the regex. This is to avoid loops.
It is possible to create two `Merge` tables that will endlessly try to read each others' data, but this is not a good idea.

The typical way to use the `Merge` engine is for working with a large number of `TinyLog` tables as if with a single table.

<h2 id="examples">
  Examples
</h2>

**Example 1**

Consider two databases `ABC_corporate_site` and `ABC_store`. The `all_visitors` table will contain IDs from the tables `visitors` in both databases.

```sql theme={null}
CREATE TABLE all_visitors (id UInt32) ENGINE=Merge(REGEXP('ABC_*'), 'visitors');
```

**Example 2**

Let's say you have an old table `WatchLog_old` and decided to change partitioning without moving data to a new table `WatchLog_new`, and you need to see data from both tables.

```sql theme={null}
CREATE TABLE WatchLog_old(
    date Date,
    UserId Int64,
    EventType String,
    Cnt UInt64
)
ENGINE=MergeTree
ORDER BY (date, UserId, EventType);

INSERT INTO WatchLog_old VALUES ('2018-01-01', 1, 'hit', 3);

CREATE TABLE WatchLog_new(
    date Date,
    UserId Int64,
    EventType String,
    Cnt UInt64
)
ENGINE=MergeTree
PARTITION BY date
ORDER BY (UserId, EventType)
SETTINGS index_granularity=8192;

INSERT INTO WatchLog_new VALUES ('2018-01-02', 2, 'hit', 3);

CREATE TABLE WatchLog AS WatchLog_old ENGINE=Merge(currentDatabase(), '^WatchLog');

SELECT * FROM WatchLog;
```

```text theme={null}
┌───────date─┬─UserId─┬─EventType─┬─Cnt─┐
│ 2018-01-01 │      1 │ hit       │   3 │
└────────────┴────────┴───────────┴─────┘
┌───────date─┬─UserId─┬─EventType─┬─Cnt─┐
│ 2018-01-02 │      2 │ hit       │   3 │
└────────────┴────────┴───────────┴─────┘
```

<h2 id="virtual-columns">
  Virtual columns
</h2>

* `_table` — The name of the table from which data was read. Type: [String](/reference/data-types/string).

  If you filter on `_table`, (for example `WHERE _table='xyz'`) only tables which satisfy the filter condition are read.

* `_database` — Contains the name of the database from which data was read. Type: [String](/reference/data-types/string).

**See Also**

* [Virtual columns](/reference/engines/table-engines/index#table_engines-virtual_columns)
* [merge](/reference/functions/table-functions/merge) table function