INSERT … SELECTで別テーブルにデータをコピーする方法

引っ越し用の段ボール箱 SQL

古い注文を保存用のテーブルに移す。本番のデータの一部を検証用のテーブルに写す。どちらも、SELECT の結果をそのまま INSERT に渡す INSERT … SELECT を使えば1文で書けます。書き方は短いのですが、列の順番がずれていてもエラーにならずに入ってしまったり、2回流すと主キーが重なって止まったりします。対象を SELECT で確かめてからコピーする手順、重複を避ける書き方、CREATE TABLE … SELECT で作ったテーブルに主キーが付かない理由まで、実行結果を見ながら確かめます。

以下の結果は MySQL 8.0(8.0.46)で実際に実行したものです。

このシナリオで考える

文房具のネットショップで、注文テーブル orders がふくらんできたとします。2025年に発送が終わった注文は、保存用の orders_archive に写すことにしました。保存用のテーブルには、写した日を入れる archived_on 列があり、こちらは元の orders にはありません。

CREATE TABLE orders (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  customer_name VARCHAR(50) NOT NULL,
  item          VARCHAR(50) NOT NULL,
  amount        INT NOT NULL,
  status        VARCHAR(20) NOT NULL,
  ordered_at    DATE NOT NULL
);

INSERT INTO orders (customer_name, item, amount, status, ordered_at) VALUES
  ('佐藤', 'ノート',       300, '発送済み',   '2025-11-02'),
  ('鈴木', 'ボールペン',   150, 'キャンセル', '2025-11-15'),
  ('高橋', '付箋',         200, '発送済み',   '2025-12-01'),
  ('田中', 'ファイル',     450, '準備中',     '2025-12-20'),
  ('佐藤', 'のり',         120, '発送済み',   '2026-01-10'),
  ('伊藤', 'ノート',       300, '発送済み',   '2026-02-03'),
  ('鈴木', 'はさみ',       500, '準備中',     '2026-02-14');

CREATE TABLE orders_archive (
  id            INT PRIMARY KEY,
  customer_name VARCHAR(50) NOT NULL,
  item          VARCHAR(50) NOT NULL,
  amount        INT NOT NULL,
  ordered_at    DATE NOT NULL,
  archived_on   DATE NOT NULL
);

INSERT … SELECT の基本

INSERT … SELECT は、VALUES の代わりに SELECT 文を書く INSERT です。SELECT が返した行が、そのまま挿入先に入ります。いきなり INSERT を流さずに、次の順で進めると安全です。

  1. SELECT だけを実行して、写す行と件数を確かめる
  2. 同じ SELECT の前に INSERT INTO テーブル (列...) をつけて実行する
  3. 挿入した件数と、挿入先の中身を確かめる

まず、2025年中に発送済みになった注文を SELECT で出します。

SELECT id, customer_name, item, amount, ordered_at
FROM orders
WHERE ordered_at < '2026-01-01'
  AND status = '発送済み';
+----+---------------+-----------+--------+------------+
| id | customer_name | item      | amount | ordered_at |
+----+---------------+-----------+--------+------------+
|  1 | 佐藤          | ノート    |    300 | 2025-11-02 |
|  3 | 高橋          | 付箋      |    200 | 2025-12-01 |
+----+---------------+-----------+--------+------------+

2件で合っているので、この SELECT の前に INSERT をつけます。元のテーブルにない archived_on には、SELECT の列に日付の値をそのまま書けば入ります。

INSERT INTO orders_archive
  (id, customer_name, item, amount, ordered_at, archived_on)
SELECT id, customer_name, item, amount, ordered_at, '2026-11-18'
FROM orders
WHERE ordered_at < '2026-01-01'
  AND status = '発送済み';

SELECT ROW_COUNT();  -- 直前の INSERT で入った件数
SELECT * FROM orders_archive ORDER BY id;
+-------------+
| ROW_COUNT() |
+-------------+
|           2 |
+-------------+

+----+---------------+-----------+--------+------------+-------------+
| id | customer_name | item      | amount | ordered_at | archived_on |
+----+---------------+-----------+--------+------------+-------------+
|  1 | 佐藤          | ノート    |    300 | 2025-11-02 | 2026-11-18  |
|  3 | 高橋          | 付箋      |    200 | 2025-12-01 | 2026-11-18  |
+----+---------------+-----------+--------+------------+-------------+

SELECT で確かめた2件が、そのまま入りました。SELECT には WHERE だけでなく JOIN や GROUP BY も書けるので、集計した結果を別のテーブルに残す使い方もできます。

ORDER BY のない SELECT は、行の順番が決まっていません。挿入先に AUTO_INCREMENT の列があって番号を振らせるときや、レプリカ(複製先のサーバー)があるときは、ORDER BY をつけて順番を固定しておくと、サーバーごとに番号がずれるのを防げます。

列の数と順番をそろえる

INSERT 側の列の並びと、SELECT 側の列の並びは、数も順番も合わせます。数が合わないときは、MySQL がエラーで止めてくれます。

-- archived_on の値を書き忘れた
INSERT INTO orders_archive
  (id, customer_name, item, amount, ordered_at, archived_on)
SELECT id, customer_name, item, amount, ordered_at
FROM orders
WHERE id = 5;
ERROR 1136 (21S01): Column count doesn't match value count at row 1

困るのは、数は合っていて順番だけがずれているときです。型が同じ列どうしなら、エラーにならずにそのまま入ります。次の例では、INSERT 側の列名を省き、SELECT で item と customer_name を逆に書いています。

CREATE TABLE order_names (
  id            INT PRIMARY KEY,
  customer_name VARCHAR(50),
  item          VARCHAR(50)
);

INSERT INTO order_names
SELECT id, item, customer_name
FROM orders
WHERE id <= 3;

SELECT * FROM order_names ORDER BY id;
+----+-----------------+--------+
| id | customer_name   | item   |
+----+-----------------+--------+
|  1 | ノート          | 佐藤   |
|  2 | ボールペン      | 鈴木   |
|  3 | 付箋            | 高橋   |
+----+-----------------+--------+

顧客名の列に商品名が入りました。挿入先の列名を書かないと、MySQL はテーブルを作ったときの列の順番で値を当てはめます。SELECT 側の名前は見ていません。

書き方 起きること
列の数が合わない ERROR 1136 で止まる
数は合うが順番がずれ、型も合う エラーにならず、違う列に値が入る
数は合うが順番がずれ、型が合わない 型の変換エラーで止まることが多い
INSERT 側にも SELECT 側にも列名を書く 並びを目で見比べられる

挿入先の列名は毎回書き、SELECT 側も * を使わずに列名を並べておきましょう。テーブルにあとから列が足されたときにも、ずれずに済みます。

2回目の実行で重複させない

同じ INSERT … SELECT をもう一度流すと、すでに入っている id とぶつかって止まります。毎月の保存作業のように同じ文をくり返すときは、ここをどうするかを先に決めておきます。

ERROR 1062 (23000): Duplicate entry '1' for key 'orders_archive.PRIMARY'

まだ入っていない行だけを足すなら、NOT EXISTS で挿入先にない行に絞ります。田中さんの注文(id 4)があとから発送済みになったとして、もう一度流してみます。

UPDATE orders SET status = '発送済み' WHERE id = 4;

INSERT INTO orders_archive
  (id, customer_name, item, amount, ordered_at, archived_on)
SELECT o.id, o.customer_name, o.item, o.amount, o.ordered_at, '2026-11-18'
FROM orders AS o
WHERE o.ordered_at < '2026-01-01'
  AND o.status = '発送済み'
  AND NOT EXISTS (
    SELECT 1 FROM orders_archive AS a WHERE a.id = o.id
  );

SELECT ROW_COUNT();
SELECT id, customer_name, item, ordered_at FROM orders_archive ORDER BY id;
+-------------+
| ROW_COUNT() |
+-------------+
|           1 |
+-------------+

+----+---------------+--------------+------------+
| id | customer_name | item         | ordered_at |
+----+---------------+--------------+------------+
|  1 | 佐藤          | ノート       | 2025-11-02 |
|  3 | 高橋          | 付箋         | 2025-12-01 |
|  4 | 田中          | ファイル     | 2025-12-20 |
+----+---------------+--------------+------------+

新しく対象になった id 4 だけが入りました。INSERT IGNORE でも重複は飛ばせます。同じ条件で流すと、3件とも重複として飛ばされ、警告が残ります。

INSERT IGNORE INTO orders_archive
  (id, customer_name, item, amount, ordered_at, archived_on)
SELECT id, customer_name, item, amount, ordered_at, '2026-11-18'
FROM orders
WHERE ordered_at < '2026-01-01'
  AND status = '発送済み';

SHOW WARNINGS;
+---------+------+------------------------------------------------------+
| Level   | Code | Message                                              |
+---------+------+------------------------------------------------------+
| Warning | 1062 | Duplicate entry '1' for key 'orders_archive.PRIMARY' |
| Warning | 1062 | Duplicate entry '3' for key 'orders_archive.PRIMARY' |
| Warning | 1062 | Duplicate entry '4' for key 'orders_archive.PRIMARY' |
+---------+------+------------------------------------------------------+
書き方 重複した行 向いている場面
NOT EXISTS で絞る 最初から選ばれない 何を足すかを SQL の条件で読み取れるようにしたいとき
INSERT IGNORE 飛ばして警告にする 手早く済ませたいとき。ほかのエラーも警告に変わる点に注意
ON DUPLICATE KEY UPDATE 入っている行を新しい値で更新する 写したあとに元の値が変わることがあるとき

INSERT IGNORE は、重複以外のエラーも警告に変えます。長すぎる値は切り詰められ、NOT NULL の列への NULL は 0 や空の文字列に置きかえられたまま入ります。どの行がなぜ飛ばされたのかを後から追いにくいので、くり返し流す処理には NOT EXISTS を選ぶのが無難です。値を上書きしたいときの ON DUPLICATE KEY UPDATE の書き方は「MySQLのON DUPLICATE KEY UPDATEの使い方」で説明しています。

CREATE TABLE … SELECT との違い

挿入先のテーブルがまだないときは、CREATE TABLE … SELECT でテーブル作りとコピーを1文で済ませられます。ただし、できあがるテーブルには主キーもインデックスも付きません。CREATE TABLE … LIKE で同じ形の空のテーブルを作ってから INSERT … SELECT したものと比べます。

CREATE TABLE orders_copy1 AS SELECT * FROM orders;

CREATE TABLE orders_copy2 LIKE orders;
INSERT INTO orders_copy2 SELECT * FROM orders;

SELECT TABLE_NAME, COLUMN_NAME, COLUMN_KEY, EXTRA
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME IN ('orders', 'orders_copy1', 'orders_copy2')
  AND COLUMN_NAME = 'id'
ORDER BY TABLE_NAME;
+--------------+-------------+------------+----------------+
| TABLE_NAME   | COLUMN_NAME | COLUMN_KEY | EXTRA          |
+--------------+-------------+------------+----------------+
| orders       | id          | PRI        | auto_increment |
| orders_copy1 | id          |            |                |
| orders_copy2 | id          | PRI        | auto_increment |
+--------------+-------------+------------+----------------+

どちらにも7件のデータは写っています。それでも orders_copy1 の id は主キーでも AUTO_INCREMENT でもなく、ただの INT の列です。このテーブルをそのまま使い続けると、同じ id の行が入ってもはじかれず、id での検索も全件を読むことになります。

方法 主キー・インデックス AUTO_INCREMENT 向いている場面
CREATE TABLE … SELECT 付かない 付かない 集計結果の一時的な保存、作業前の控え
CREATE TABLE … LIKE と INSERT … SELECT 元と同じ 元と同じ 元と同じ形で使い続けるコピー
先に CREATE TABLE で形を決めて INSERT … SELECT 自分で決める 自分で決める 列を減らす・足すなど、形を変えたいとき

CREATE TABLE … LIKE も、外部キーの定義までは写しません。外部キーが要るときは、あとから ALTER TABLE で足します。

よくある落とし穴

長すぎる値が1件でもあると、全部入らない

挿入先の列が短いと、strict モード(MySQL 8.0 の初期設定)ではエラーになります。次の例では、3文字までの列に「ボールペン」を入れようとして止まりました。

CREATE TABLE short_names (
  id   INT PRIMARY KEY,
  item VARCHAR(3)
);

INSERT INTO short_names
SELECT id, item FROM orders WHERE id IN (1, 2);

SELECT COUNT(*) AS short_rows FROM short_names;
ERROR 1265 (01000): Data truncated for column 'item' at row 2

+------------+
| short_rows |
+------------+
|          0 |
+------------+

1行目の「ノート」は3文字なので入る長さです。それでも InnoDB では文がまるごと取り消され、件数は0件のままでした。途中まで入って半端な状態になることはありません。

同じテーブルにも写せる

挿入先と同じテーブルを FROM に書いてもかまいません。MySQL は SELECT の結果をいったん内部の一時テーブルに置いてから挿入します。過去の注文を元に、同じ内容の新しい注文を作る例です。

INSERT INTO orders (customer_name, item, amount, status, ordered_at)
SELECT customer_name, item, amount, '準備中', '2026-11-18'
FROM orders
WHERE id = 6;

SELECT id, customer_name, item, status, ordered_at FROM orders WHERE id >= 6 ORDER BY id;
+----+---------------+-----------+--------------+------------+
| id | customer_name | item      | status       | ordered_at |
+----+---------------+-----------+--------------+------------+
|  6 | 伊藤          | ノート    | 発送済み     | 2026-02-03 |
|  7 | 鈴木          | はさみ    | 準備中       | 2026-02-14 |
|  8 | 伊藤          | ノート    | 準備中       | 2026-11-18 |
+----+---------------+-----------+--------------+------------+

読み元の行にもロックがかかる

MySQL の初期設定の分離レベル(REPEATABLE READ)では、INSERT … SELECT は読み元の行に共有ロックをかけます。コピーが終わるまで、その行を UPDATE や DELETE する処理は待たされます。何百万行もあるテーブルを1文で写すと、そのあいだ注文の更新が止まることがあります。大きいテーブルは id の範囲で区切って少しずつ写しましょう。区切り方は「大量データのDELETE・UPDATEをLIMITで分割する方法」と同じ考え方で書けます。

移すなら、コピーと削除を1つのトランザクションに

保存用に写した行を元のテーブルから消すときは、INSERT と DELETE を同じトランザクションに入れます。片方だけ成功すると、行が消えたり2か所に残ったりします。消すときは、写した先にある行だけを JOIN で選ぶと、写し損ねた行を消さずに済みます。

START TRANSACTION;

-- ここで INSERT ... SELECT を実行しておく

DELETE o
FROM orders AS o
JOIN orders_archive AS a ON a.id = o.id;

COMMIT;

SELECT id, customer_name, item, status FROM orders ORDER BY id;
+----+---------------+-----------------+-----------------+
| id | customer_name | item            | status          |
+----+---------------+-----------------+-----------------+
|  2 | 鈴木          | ボールペン      | キャンセル      |
|  5 | 佐藤          | のり            | 発送済み        |
|  6 | 伊藤          | ノート          | 発送済み        |
|  7 | 鈴木          | はさみ          | 準備中          |
|  8 | 伊藤          | ノート          | 準備中          |
+----+---------------+-----------------+-----------------+

保存用に写した id 1・3・4 の3件だけが消えました。

まとめ

INSERT … SELECT は、SELECT で写す行を確かめてから、その前に INSERT をつけるだけで書けます。挿入先の列名は毎回書いて、順番のずれで違う列に値が入るのを防ぎます。くり返し流すなら NOT EXISTS で入っていない行に絞り、テーブルごと写すなら CREATE TABLE … SELECT ではなく LIKE で形を写してから INSERT … SELECT すると、主キーとインデックスが残ります。

もっと学ぶなら【PR】

※Amazonのアソシエイトとして、当サイトは適格販売により収入を得ています。

参考リンク

アイキャッチ画像: Photo by Kelli McClintock on Unsplash

タイトルとURLをコピーしました