> ## 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.

# How to insert all rows from one table to another?

> Knowledgebase article on how to insert all rows from one table to another.

{frontMatter.description}

<h2 id="introduction">
  Introduction
</h2>

Sometimes you need to reingest all the data from one table to another.

For example, you might want to reingest data from a staging table to a production table. This article shows how to do this using the `INSERT INTO` statement.

<h2 id="example">
  Example
</h2>

Below is a simple example on how it works and how to test:

* Create a sample database

```sql theme={null}
CREATE DATABASE db1;
```

* Create a sample table

```sql theme={null}
CREATE TABLE db1.source_table
(
    city VARCHAR,
    country VARCHAR,
    continent VARCHAR
)
engine = MergeTree()
ORDER BY continent;
```

* Insert some data into the source table

```sql theme={null}
INSERT INTO db1.source_table (city, country, continent)
VALUES
    ('New York', 'USA', 'North America'),
    ('Tokyo', 'Japan', 'Asia'),
    ('Berlin', 'Germany', 'Europe'),
    ('Paris', 'France', 'Europe'),
    ('Cairo', 'Egypt', 'Africa'),
    ('Sydney', 'Australia', 'Australia');
```

* Check the number of rows in the source table

```sql theme={null}
SELECT COUNT(*) FROM db1.source_table;
```

```text theme={null}
┌─count()─┐
│      6  │
└─────────┘
```

* Create a new table with the same structure as the source table.

```sql theme={null}
CREATE TABLE db1.target_table AS db1.source_table;
```

* Insert all rows from the source table to the target table.

```sql theme={null}
INSERT INTO db1.target_table SELECT * FROM db1.source_table;
```

* Check the number of rows in the target table

```sql theme={null}
SELECT COUNT(*) FROM db1.target_table;
```

```text theme={null}
┌─count()─┐
│      6  │
└─────────┘
```

If you want to modify the structure of the new table, you can first display the structure of the source table.

```sql theme={null}
SHOW CREATE TABLE db1.source_table;
```

Then create the new table with the modified structure. In our case we want to add a new column `population` to the target table.

```sql theme={null}
CREATE TABLE db1.target_table_population
(
    `city` String,
    `country` String,
    `continent` String,
    `population` UInt16,
)
ENGINE = MergeTree
ORDER BY continent;
```

* Insert all rows from the source table to the target table, including the new column. The population field is set to 0 for all rows.

```sql theme={null}
INSERT INTO db1.target_table_population (city, country, continent, population)
SELECT city, country, continent, 0 FROM db1.source_table;
```

* Check the data in the target table

```sql theme={null}
SELECT * FROM db1.target_table_population LIMIT 3;
```

```text theme={null}
┌─city──────┬─country───┬─continent──────┬─population─┐
│ New York  │ USA       │ North America  │          0 │
│ Tokyo     │ Japan     │ Asia           │          0 │
│ Berlin    │ Germany   │ Europe         │          0 │
└───────────┴───────────┴────────────────┴────────────┘
```