【SQL】INSERT INTO…SELECT完全ガイド｜条件絞り込み・JOIN・CASE WHEN加工・異テーブル列マッピング・アーカイブ・パフォーマンスまで解説

-- orders テーブル（注文）
-- id | customer_id | product_id | amount | status    | region | ordered_at
--  1 |           1 |        101 |  12000 | shipped   | east   | 2024-01-15 09:30:00
--  2 |           2 |        102 |   3500 | pending   | west   | 2024-02-01 15:45:00
--  3 |           3 |        101 |  85000 | shipped   | east   | 2024-02-20 11:00:00
--  4 |           1 |        103 |   2800 | canceled  | north  | 2024-03-05 08:00:00
--  5 |           4 |        102 |  45000 | pending   | west   | 2024-03-10 17:30:00
--  6 |           2 |        101 |   9500 | shipped   | south  | 2024-04-01 10:00:00

-- customers テーブル（顧客）
-- id | name   | email                | rank
--  1 | 田中   | tanaka@example.com   | gold
--  2 | 鈴木   | suzuki@example.com   | silver
--  3 | 高橋   | takahashi@example.com| gold
--  4 | 伊藤   | ito@example.com      | bronze

-- 基本構文
INSERT INTO 挿入先テーブル (列1, 列2, ...)
SELECT 列A, 列B, ...
FROM   取得元テーブル
WHERE  条件;

-- ポイント:
-- ・INSERT の列数と SELECT の列数を一致させる
-- ・データ型も一致させる（自動変換されることもあるが明示が安全）
-- ・WHERE を省略すると全行が挿入される

-- 例: shipped 注文を shipped_orders テーブルにコピー
CREATE TABLE shipped_orders LIKE orders;  -- MySQL: 同じ構造のテーブルを作成

INSERT INTO shipped_orders (id, customer_id, product_id, amount, status, region, ordered_at)
SELECT                      id, customer_id, product_id, amount, status, region, ordered_at
FROM orders
WHERE status = 'shipped';
-- → id: 1, 3, 6 の 3 行が挿入される

-- 単一条件: キャンセル注文をアーカイブテーブルに移す
INSERT INTO orders_archive (id, customer_id, amount, status, ordered_at, archived_at)
SELECT                      id, customer_id, amount, status, ordered_at, NOW()
FROM orders
WHERE status = 'canceled';
-- archived_at 列に NOW() を挿入（SELECT 元にない値を付加できる）

-- 複数条件（AND / OR）
INSERT INTO high_value_shipped
SELECT *
FROM orders
WHERE status = 'shipped'
  AND amount >= 10000;
-- shipped かつ 10000 以上の注文だけを挿入

-- 日付範囲で絞り込み
INSERT INTO orders_2024q1
SELECT *
FROM orders
WHERE ordered_at >= '2024-01-01'
  AND ordered_at <  '2024-04-01';

-- IN での複数値絞り込み
INSERT INTO priority_orders
SELECT *
FROM orders
WHERE region IN ('east', 'west')
  AND status IN ('pending', 'shipped');

-- NULL チェック
INSERT INTO orders_no_customer
SELECT *
FROM orders
WHERE customer_id IS NULL;

-- 注文情報と顧客情報を結合して注文サマリーテーブルに挿入
-- 挿入先テーブル構造:
-- order_summary (order_id, customer_name, customer_email, customer_rank, amount, region, ordered_at)
INSERT INTO order_summary (order_id, customer_name, customer_email, customer_rank, amount, region, ordered_at)
SELECT
    o.id,
    c.name,
    c.email,
    c.rank,
    o.amount,
    o.region,
    o.ordered_at
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'shipped';
-- JOIN で顧客情報を付加して挿入

-- LEFT JOIN: 顧客情報がない注文も挿入（customer_name は NULL）
INSERT INTO order_summary (order_id, customer_name, amount, region)
SELECT
    o.id,
    c.name,        -- 顧客が見つからない場合は NULL
    o.amount,
    o.region
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.id;

-- サブクエリで集計した結果を挿入
INSERT INTO monthly_summary (year_month, order_count, total_amount)
SELECT
    DATE_FORMAT(ordered_at, '%Y-%m') AS year_month,
    COUNT(*) AS order_count,
    SUM(amount) AS total_amount
FROM orders
WHERE status = 'shipped'
GROUP BY DATE_FORMAT(ordered_at, '%Y-%m');

-- status を日本語ラベルに変換して挿入
INSERT INTO orders_ja (id, customer_id, amount, status_ja, ordered_at)
SELECT
    id,
    customer_id,
    amount,
    CASE status
        WHEN 'shipped'  THEN '発送済み'
        WHEN 'pending'  THEN '処理中'
        WHEN 'canceled' THEN 'キャンセル'
        ELSE '不明'
    END AS status_ja,
    ordered_at
FROM orders;

-- 金額を区分ラベルに変換して分類テーブルに挿入
INSERT INTO order_categories (order_id, amount, category, tax_amount, tax_rate)
SELECT
    id,
    amount,
    CASE
        WHEN amount >= 50000 THEN 'large'
        WHEN amount >= 10000 THEN 'medium'
        ELSE 'small'
    END AS category,
    ROUND(amount * 0.10) AS tax_amount,   -- 10% 消費税を計算して挿入
    0.10                  AS tax_rate
FROM orders
WHERE status != 'canceled';

-- 文字列・日付関数で加工
INSERT INTO orders_normalized (order_id, region_upper, order_date, order_year, order_month)
SELECT
    id,
    UPPER(region) AS region_upper,      -- 大文字に変換
    DATE(ordered_at) AS order_date,     -- 日付部分のみ
    YEAR(ordered_at) AS order_year,
    MONTH(ordered_at) AS order_month
FROM orders;

-- orders（7列）→ orders_lite（4列）に絞り込んで挿入
-- orders_lite (order_id, total, region, created_at)
INSERT INTO orders_lite (order_id, total, region, created_at)
SELECT        id,       amount, region, ordered_at  -- 列名も順序も違うが対応させる
FROM orders
WHERE status = 'shipped';

-- 不要な列は SELECT から省くだけでよい
-- 余分な列は INSERT の列リストに含めなければ DEFAULT 値 or NULL が入る

-- NULL や定数を補完して挿入（SELECT 元にない列を持つテーブルへ）
-- orders_with_meta (id, amount, status, imported_at, import_source)
INSERT INTO orders_with_meta (id, amount, status, imported_at, import_source)
SELECT
    id,
    amount,
    status,
    NOW() AS imported_at,       -- 現在日時を定数として挿入
    'migration_2024' AS import_source  -- 文字列定数を挿入
FROM orders;

-- NULL を意図的に補完する列
INSERT INTO orders_extended (id, amount, note)
SELECT
    id,
    amount,
    NULL AS note    -- note は後から入力予定なので NULL で挿入
FROM orders
WHERE status = 'pending';

-- 特定の注文をコピーして別の顧客向けに複製（id は AUTO_INCREMENT で自動採番）
INSERT INTO orders (customer_id, product_id, amount, status, region, ordered_at)
SELECT
    5 AS customer_id,      -- 新しい顧客 ID
    product_id,
    amount,
    'pending' AS status,   -- 新規注文としてステータスをリセット
    region,
    NOW() AS ordered_at    -- 現在日時を設定
FROM orders
WHERE id = 1;              -- id=1 の行を元に複製

-- 商品の期間限定キャンペーン価格をコピー
-- (割引セールの設定を次月にも引き継ぐ場合など)
INSERT INTO price_rules (product_id, discount_rate, valid_from, valid_to)
SELECT
    product_id,
    discount_rate,
    DATE_ADD(valid_to, INTERVAL 1 DAY) AS valid_from,   -- 終了翌日から開始
    DATE_ADD(valid_to, INTERVAL 31 DAY) AS valid_to     -- 1ヶ月延長
FROM price_rules
WHERE valid_to = CURDATE() - INTERVAL 1 DAY;  -- 昨日終了したルール

-- ===== MySQL =====
CREATE TABLE orders_backup AS
SELECT * FROM orders
WHERE ordered_at >= '2024-01-01';
-- テーブルを新規作成してデータを挿入（構造はSELECT結果から自動生成）
-- ただし PRIMARY KEY・インデックスはコピーされない

-- MySQL: 空のテーブルを複製（データなし）
CREATE TABLE orders_empty LIKE orders;
-- LIKE は制約・インデックスもコピー（CTAS はコピーしない）

-- ===== PostgreSQL =====
CREATE TABLE orders_backup AS
SELECT * FROM orders WHERE ordered_at >= '2024-01-01';

-- 空テーブルの複製
CREATE TABLE orders_empty AS SELECT * FROM orders WHERE false;

-- ===== SQL Server =====
-- SQL Server: SELECT INTO で同等の操作
SELECT * INTO orders_backup
FROM orders
WHERE ordered_at >= '2024-01-01';

-- ===== 用途の使い分け =====
-- CTAS / SELECT INTO: テーブルが存在しない場合の初回作成・バックアップ
-- INSERT INTO ... SELECT: すでに存在するテーブルへの追記・蓄積

-- 3ヶ月以上前のキャンセル注文をアーカイブに移す
BEGIN;

-- ① アーカイブテーブルに挿入
INSERT INTO orders_archive (id, customer_id, amount, status, ordered_at, archived_at)
SELECT                      id, customer_id, amount, status, ordered_at, NOW()
FROM orders
WHERE status = 'canceled'
  AND ordered_at < DATE_SUB(NOW(), INTERVAL 3 MONTH);

-- ② 元テーブルから削除（同じ WHERE 条件を使う）
DELETE FROM orders
WHERE status = 'canceled'
  AND ordered_at < DATE_SUB(NOW(), INTERVAL 3 MONTH);

COMMIT;
-- トランザクションで一括処理: 両方成功するかゼロかを保証

-- 移行件数の確認
SELECT ROW_COUNT();  -- DELETE の影響行数

-- 月次サマリーテーブルに今月分の集計を保存
-- monthly_sales (year_month, shop_id, order_count, total_amount, avg_amount, updated_at)

-- 既存の今月分を削除してから最新データを挿入（upsert 的な操作）
DELETE FROM monthly_sales
WHERE year_month = DATE_FORMAT(NOW(), '%Y-%m');

INSERT INTO monthly_sales (year_month, region, order_count, total_amount, avg_amount, updated_at)
SELECT
    DATE_FORMAT(ordered_at, '%Y-%m') AS year_month,
    region,
    COUNT(*) AS order_count,
    SUM(amount) AS total_amount,
    ROUND(AVG(amount), 0) AS avg_amount,
    NOW() AS updated_at
FROM orders
WHERE status = 'shipped'
  AND ordered_at >= DATE_FORMAT(NOW(), '%Y-%m-01')
  AND ordered_at <  DATE_FORMAT(DATE_ADD(NOW(), INTERVAL 1 MONTH), '%Y-%m-01')
GROUP BY DATE_FORMAT(ordered_at, '%Y-%m'), region;

-- 実行前に今日付きのバックアップテーブルを作成（毎日実行するバッチなど）
-- MySQL: 日付付きのバックアップテーブルを作成してコピー

-- ① テーブルを複製（構造コピー）
CREATE TABLE orders_backup_20240408 LIKE orders;

-- ② 全データをコピー
INSERT INTO orders_backup_20240408
SELECT * FROM orders;

-- または CTAS で一発
CREATE TABLE orders_backup_20240408 AS SELECT * FROM orders;

-- ③ バックアップの確認
SELECT COUNT(*) FROM orders_backup_20240408;
SELECT COUNT(*) FROM orders;
-- 件数が一致すること

-- 顧客ランク別・地域別のレポートテーブルに挿入
INSERT INTO report_order_summary (report_date, customer_rank, region, order_count, total_amount)
SELECT
    CURDATE() AS report_date,
    c.rank AS customer_rank,
    o.region,
    COUNT(*) AS order_count,
    SUM(o.amount) AS total_amount
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'shipped'
  AND o.ordered_at >= DATE_FORMAT(NOW() - INTERVAL 1 MONTH, '%Y-%m-01')
  AND o.ordered_at <  DATE_FORMAT(NOW(), '%Y-%m-01')
GROUP BY c.rank, o.region;
-- 前月のデータを集計してレポートテーブルに毎月1日に実行するバッチ

-- ===== MySQL: INSERT IGNORE（重複はスキップ）=====
INSERT IGNORE INTO shipped_orders (id, customer_id, amount, status, ordered_at)
SELECT                             id, customer_id, amount, status, ordered_at
FROM orders
WHERE status = 'shipped';
-- id が PRIMARY KEY の場合、既に存在する id はスキップされる

-- ===== NOT EXISTS を使った条件付き挿入 =====
-- アーカイブテーブルに未登録のものだけ挿入
INSERT INTO orders_archive (id, customer_id, amount, status, ordered_at, archived_at)
SELECT o.id, o.customer_id, o.amount, o.status, o.ordered_at, NOW()
FROM orders o
WHERE o.status = 'canceled'
  AND NOT EXISTS (
      SELECT 1 FROM orders_archive a WHERE a.id = o.id
  );

-- ===== INSERT INTO ... SELECT ON DUPLICATE KEY UPDATE（MySQL: UPSERT）=====
INSERT INTO monthly_sales (year_month, total_amount, updated_at)
SELECT
    DATE_FORMAT(ordered_at, '%Y-%m'),
    SUM(amount),
    NOW()
FROM orders
WHERE status = 'shipped'
GROUP BY DATE_FORMAT(ordered_at, '%Y-%m')
ON DUPLICATE KEY UPDATE
    total_amount = VALUES(total_amount),
    updated_at   = VALUES(updated_at);
-- year_month が PRIMARY KEY の場合、既存行は UPDATE、新規は INSERT

-- ===== 1. バッチ分割で少量ずつ挿入（大量データ向け）=====
-- 一度に全件挿入するとトランザクションログが大量生成・テーブルロックが長時間
-- → N 件ずつ分割して繰り返す（アプリ側でループ処理）

-- 例: 1000件ずつ分割（MySQL）
INSERT INTO orders_archive (id, customer_id, amount, status, ordered_at, archived_at)
SELECT id, customer_id, amount, status, ordered_at, NOW()
FROM orders
WHERE status = 'canceled'
  AND ordered_at < DATE_SUB(NOW(), INTERVAL 3 MONTH)
ORDER BY id
LIMIT 1000;   -- 1000 件ずつ繰り返す

-- ===== 2. SELECT 部分のインデックス活用 =====
-- WHERE の列にインデックスがあること
CREATE INDEX idx_orders_status_ordered ON orders (status, ordered_at);

-- EXPLAIN で実行計画を確認
EXPLAIN
SELECT * FROM orders
WHERE status = 'canceled' AND ordered_at < '2024-01-01';
-- type: range でインデックスが使われているか確認

-- ===== 3. インデックスの一時無効化（大量一括挿入向け）=====
-- MySQL: 挿入前にインデックスを無効化（挿入後に再構築）
ALTER TABLE orders_archive DISABLE KEYS;

INSERT INTO orders_archive ...
SELECT ... FROM orders WHERE ...;

ALTER TABLE orders_archive ENABLE KEYS;
-- ※ MyISAM テーブル向け。InnoDB では innodb_autoinc_lock_mode の設定で制御

-- ===== 4. PostgreSQL: COPY コマンドが最速 =====
-- INSERT より COPY の方が大量データ挿入が速い
-- (外部ファイルや stdin から読み込む場合は COPY が有効)
-- INSERT INTO ... SELECT は COPY より遅いが、テーブル間コピーには有効

-- ===== MySQL =====
-- 基本構文（標準的）
INSERT INTO 挿入先 (col1, col2) SELECT val1, val2 FROM 元 WHERE 条件;

-- AUTO_INCREMENT の扱い: 列リストから除外すると自動採番
INSERT INTO orders_copy (customer_id, amount, status)
SELECT customer_id, amount, status FROM orders;  -- id は自動採番

-- ===== PostgreSQL =====
-- 基本的に同じ構文
INSERT INTO 挿入先 (col1, col2) SELECT val1, val2 FROM 元 WHERE 条件;

-- RETURNING で挿入された行の値を取得
INSERT INTO orders_copy (customer_id, amount)
SELECT customer_id, amount FROM orders WHERE status = 'shipped'
RETURNING id, customer_id;  -- 挿入した id を返す

-- ===== SQL Server =====
-- 基本構文は同じ
INSERT INTO 挿入先 (col1, col2) SELECT val1, val2 FROM 元 WHERE 条件;

-- SELECT INTO で新規テーブルにコピー（INSERT INTO の代替）
SELECT col1, col2 INTO 新テーブル FROM 元テーブル WHERE 条件;
-- → 新テーブルが存在しない場合に新規作成しながら挿入（CTAS 相当）

-- OUTPUT INSERTED で挿入した行を確認
INSERT INTO orders_copy (customer_id, amount, status)
OUTPUT INSERTED.id, INSERTED.customer_id
SELECT customer_id, amount, status FROM orders WHERE status = 'shipped';

-- ===== Oracle =====
-- 基本構文は同じ
INSERT INTO 挿入先 (col1, col2) SELECT val1, val2 FROM 元 WHERE 条件;

-- INSERT ALL: 複数テーブルへの同時挿入（Oracle 固有）
INSERT ALL
    INTO orders_east (id, amount) VALUES (id, amount)
    INTO orders_summary (region, amount) VALUES (region, amount)
SELECT id, amount, region
FROM orders
WHERE status = 'shipped';