Comment insérer toutes les lignes d’une table vers une autre ?

Introduction

Il est parfois nécessaire de réingérer toutes les données d'une table vers une autre.

Par exemple, vous pouvez vouloir réingérer des données d'une table intermédiaire vers une table de production. Cet article explique comment procéder à l'aide de l'instruction INSERT INTO.

Exemple

Voici un exemple simple de son fonctionnement et de la façon de le tester :

Créez une base de données de test

CREATE DATABASE db1;

Créer une table d'exemple

CREATE TABLE db1.source_table
(
    city VARCHAR,
    country VARCHAR,
    continent VARCHAR
)
engine = MergeTree()
ORDER BY continent;

Insérez des données dans la table source

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');

Vérifiez le nombre de lignes de la table source

SELECT COUNT(*) FROM db1.source_table;

┌─count()─┐
│      6  │
└─────────┘

Créez une nouvelle table avec la même structure que la table source.

CREATE TABLE db1.target_table AS db1.source_table;

Insérez toutes les lignes de la table source dans la table cible.

INSERT INTO db1.target_table SELECT * FROM db1.source_table;

Vérifiez le nombre de lignes de la table cible

SELECT COUNT(*) FROM db1.target_table;

┌─count()─┐
│      6  │
└─────────┘

Si vous souhaitez modifier la structure de la nouvelle table, vous pouvez d’abord consulter la structure de la table source.

SHOW CREATE TABLE db1.source_table;

Créez ensuite la nouvelle table avec la structure modifiée. Dans notre cas, nous voulons ajouter une nouvelle colonne population à la table cible.

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

Insérez toutes les lignes de la table source dans la table cible, y compris la nouvelle colonne. Le champ population est défini à 0 pour toutes les lignes.

INSERT INTO db1.target_table_population (city, country, continent, population)
SELECT city, country, continent, 0 FROM db1.source_table;

Vérifiez les données de la table cible

SELECT * FROM db1.target_table_population LIMIT 3;

┌─city──────┬─country───┬─continent──────┬─population─┐
│ New York  │ USA       │ North America  │          0 │
│ Tokyo     │ Japan     │ Asia           │          0 │
│ Berlin    │ Germany   │ Europe         │          0 │
└───────────┴───────────┴────────────────┴────────────┘