DB(MySQL)で他のテーブルからデータをコピーする方法

今回はデータベーステーブル間のデータコピー、特に一部のレコードをコピーする方法について取り扱います。

既存テーブルのコピーだけであれば、phpMyAdminやadminerなどのGUIが充実しているため難しくはありません。
ほぼSQLを書かずに実行できます。

コピーするデータの絞り込みや加工など、より実践的な内容を取り扱います。

この記事で分かること

データベース間でデータをコピーする方法
コピーするデータを加工する方法

サンプルのテーブルについて

今回のテーマをイメージしてもらうため、まずはサンプルデータの構造を考えてみます。
複雑にしても大変なのでかなり簡素なものにします。

・社員テーブル(元のテーブル)

社員ID(INT)	社員名(VC255)	通勤手段(VC255)	退社済みフラグ(tinyINT)
1	佐藤	徒歩
2	田中	徒歩
3	鈴木	自転車	1
4	山田	公共交通機関	1
5	斉藤	自動車

・社員テーブル2(コピー先のテーブル)

社員ID(INT)	社員名(VC255)	通勤手段番号(INT)
1	佐藤	1
2	田中	1
5	斉藤	4

・通勤手段テーブル(※作成済みとする)

通勤手段番号(INT)	通勤手段の詳細(VC255)
1	徒歩
2	自転車
3	公共交通機関
4	自動車

ざっくりまとめると、次の変更を加えたものをコピー後のテーブルとします。

退社済みの社員データは削除し管理しないようにする
通勤手段をリレーションテーブルで管理する

テーブル間のデータ挿入は「INSERT」文で行える

テーブル同士でデータを挿入する場合は、通常の追加と同じく「INSERT」文を使用します。

特定の行・列だけコピーする構文はなく、SELECTやWHEREで絞り込みます。

まずはそのまま新しいテーブルにコピーする例をみてみましょう。


INSERT INTO 社員テーブル2 (
    `社員ID`,
    `社員名`,
    `通勤手段番号`
) SELECT
    `社員ID`,
    `社員名`,
    -- ※通勤手段番号は型違いのため固定値
    0 as num
FROM 社員テーブル;

上の例では、「社員ID」と「社員名」はそのまま、「運動手段」は文字列と数値で型が違うためダミーデータを挿入しています。

できあがるテーブルは以下の通りです。

社員ID(INT)	社員名(VC255)	通勤手段番号(INT)
1	佐藤	0
2	田中	0
3	鈴木	0
4	山田	0
5	斉藤	0

ほぼ1つ目の社員テーブルから変わっていません。
ここから加工を加えていき、テーブル2の形になるようにしていきます。

INSERTするデータを加工する場合は「CASE」文を使う

データの加工をする場合も、通常のSQLと同様にCASEを使うことができます。

今回のサンプルでは、「通勤手段」(文字列)を「通勤手段番号」(INT)に変換します。

まずはSQL文を見てみましょう。


INSERT INTO 社員テーブル2 (
    `社員ID`,
    `社員名`,
    `通勤手段番号`
) SELECT
    `社員ID`,
    `社員名`,
    CASE
        WHEN `通勤手段` = '徒歩' THEN 1
        WHEN `通勤手段` = '自転車' THEN 2
        WHEN `通勤手段` = '公共交通機関' THEN 3
        WHEN `通勤手段` = '自動車' THEN 4
        ELSE 0
    END AS 通勤手段番号
FROM 社員テーブル
WHERE `退社済みフラグ` <> 1;

先ほどの例と比べると、INSERTは同じですがそのあとの構文が異なります。

ちなみにas句の別名は、予約語以外であれば何でもいいです。