SQLテーブルコピー｜CREATE TABLE AS SELECTと構造のみ

SQLでテーブルをコピーする基本は、CREATE TABLE 新テーブル AS SELECT * FROM 元テーブル です。 この書き方は CTAS（シータス）とも呼ばれ、SELECT の結果をそのまま新しいテーブルとして保存します。構造だけをコピーしたいときは WHERE 1=0 を付けるか CREATE TABLE … LIKE を使い、すでにあるテーブルへ追記したいときは INSERT INTO … SELECT を使います。

ただし、CTAS でコピーされるのは基本的に「列とデータ」だけで、主キーやインデックスは引き継がれません。 しかも何が残って何が消えるかは、データベースと方法の組み合わせで変わります。この記事では、MySQL 8.0.46・PostgreSQL 16.15・SQLite 3.45.1 で実際にコピーを行い、主キー・UNIQUE・インデックス・NOT NULL・デフォルト値・CHECK・自動採番（AUTO_INCREMENT など）が残るかを方法別に確認した結果表を載せます。消えた制約を付け直すSQLもコピペできる形で用意しました。SQL Server と Oracle は今回の環境で動かせなかったため、書き方を表に載せたうえで「未実行」と明記しています。

テーブルコピーの早見表

DB別に「データごと」「構造のみ」「既存テーブルへ追記」の書き方をまとめます。src が元のテーブル、dst が新しいテーブルです。

方法ごとの特徴は次のとおりです。

制約ごと複製したいなら「LIKE で構造を作り、INSERT SELECT でデータを入れる」2段階が、MySQL と PostgreSQL ではいちばん確実です。

この記事で使うサンプルテーブル

コピー後に何が残るかを調べるため、主キー・自動採番・UNIQUE・NOT NULL・デフォルト値・CHECK・インデックスをひととおり持つ items（備品）テーブルを用意しました。

-- MySQL 8.0
CREATE TABLE items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(20) NOT NULL UNIQUE,
  name VARCHAR(50) NOT NULL,
  price INT NOT NULL DEFAULT 0,
  status VARCHAR(10) DEFAULT 'active',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT chk_price CHECK (price >= 0),
  INDEX idx_items_name (name)
);

INSERT INTO items (code,name,price) VALUES
('A001','pen',120),('A002','note',300),('A003','eraser',80);

-- PostgreSQL 16
CREATE TABLE items (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  code VARCHAR(20) NOT NULL UNIQUE,
  name VARCHAR(50) NOT NULL,
  price INT NOT NULL DEFAULT 0,
  status VARCHAR(10) DEFAULT 'active',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT chk_price CHECK (price >= 0)
);
CREATE INDEX idx_items_name ON items(name);

INSERT INTO items (code,name,price) VALUES
('A001','pen',120),('A002','note',300),('A003','eraser',80);

-- SQLite 3
CREATE TABLE items (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  code TEXT NOT NULL UNIQUE,
  name TEXT NOT NULL,
  price INTEGER NOT NULL DEFAULT 0,
  status TEXT DEFAULT 'active',
  created_at TEXT DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT chk_price CHECK (price >= 0)
);
CREATE INDEX idx_items_name ON items(name);

INSERT INTO items (code,name,price) VALUES
('A001','pen',120),('A002','note',300),('A003','eraser',80);

コピー後に何が残るかの結果表

先に結論の表を載せます。「残る」は元と同じ定義がコピー先にもあること、「消える」はコピー先に引き継がれないことを表します。すべて今回実行して、MySQL は SHOW CREATE TABLE、PostgreSQL は \d、SQLite は .schema で確認した結果です。

表から読み取れるポイントは3つです。

• CTAS で主キー・UNIQUE・インデックス・CHECK・自動採番が消えるのは、3つのDBで共通
• MySQL の CTAS は NOT NULL とデフォルト値を残すが、PostgreSQL と SQLite の CTAS は残さない
• 外部キーは LIKE でもコピーされない（MySQL・PostgreSQL とも確認）

SQL Server と Oracle は未実行のため、この表には入れていません。一般に知られた仕様としては、SQL Server の SELECT INTO は列の型と NULL を許すかどうか、IDENTITY（自動採番）の性質を引き継ぎ、主キー・インデックス・デフォルト値・CHECK は引き継ぎません。Oracle の CTAS は明示的に付けた NOT NULL を引き継ぎ、主キー・インデックス・デフォルト値は引き継ぎません。利用するバージョンで、コピー後の定義を確認してから使うのが確実です。

以下、DBごとに実際の出力を見ていきます。

MySQLでテーブルをコピーする

CREATE TABLE AS SELECT（データごと）

CREATE TABLE items_ctas AS SELECT * FROM items;
SHOW CREATE TABLE items_ctas\G

実行結果（MySQL 8.0.46）：

Create Table: CREATE TABLE `items_ctas` (
  `id` int NOT NULL DEFAULT '0',
  `code` varchar(20) NOT NULL,
  `name` varchar(50) NOT NULL,
  `price` int NOT NULL DEFAULT '0',
  `status` varchar(10) DEFAULT 'active',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

データ3行はコピーされ、NOT NULL と DEFAULT は残っています。一方で、PRIMARY KEY・UNIQUE KEY・KEY idx_items_name・CHECK の行がなくなり、id は AUTO_INCREMENT ではなく DEFAULT '0' に変わりました。

この状態で元テーブルと同じ感覚で INSERT すると、重複したコードが入り、id は 0 になります。

INSERT INTO items_ctas (code,name) VALUES ('A001','dup');
SELECT id, code, name, price, status FROM items_ctas;

+----+------+--------+-------+--------+
| id | code | name   | price | status |
+----+------+--------+-------+--------+
|  1 | A001 | pen    |   120 | active |
|  2 | A002 | note   |   300 | active |
|  3 | A003 | eraser |    80 | active |
|  0 | A001 | dup    |     0 | active |
+----+------+--------+-------+--------+

バックアップや一時的な分析用として使うなら CTAS で十分ですが、元テーブルの代わりとして使い続けるなら、制約を付け直すか、次の LIKE を使います。

CREATE TABLE LIKE（構造のみ・制約ごと）

CREATE TABLE items_like LIKE items;
INSERT INTO items_like SELECT * FROM items;
SHOW CREATE TABLE items_like\G

実行結果（MySQL 8.0.46）：

Create Table: CREATE TABLE `items_like` (
  `id` int NOT NULL AUTO_INCREMENT,
  `code` varchar(20) NOT NULL,
  `name` varchar(50) NOT NULL,
  `price` int NOT NULL DEFAULT '0',
  `status` varchar(10) DEFAULT 'active',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`),
  KEY `idx_items_name` (`name`),
  CONSTRAINT `items_like_chk_1` CHECK ((`price` >= 0))
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

主キー・UNIQUE・インデックス・AUTO_INCREMENT がすべて残っています。CHECK 制約も残りますが、名前は chk_price から items_like_chk_1 に変わりました（制約名はデータベース内で重複できないため、自動で付け直されます）。

UNIQUE が効いていること、採番が続きから行われることも確認しました。

INSERT INTO items_like (code,name) VALUES ('A001','dup');
-- ERROR 1062 (23000): Duplicate entry 'A001' for key 'items_like.code'

INSERT INTO items_like (code,name) VALUES ('A004','clip');
SELECT id, code, name, price, status FROM items_like;

+----+------+--------+-------+--------+
| id | code | name   | price | status |
+----+------+--------+-------+--------+
|  1 | A001 | pen    |   120 | active |
|  2 | A002 | note   |   300 | active |
|  3 | A003 | eraser |    80 | active |
|  5 | A004 | clip   |     0 | active |
+----+------+--------+-------+--------+

id が 4 ではなく 5 になっているのは、直前にエラーになった INSERT が番号を1つ使ったためです（AUTO_INCREMENT の番号は、エラーになっても戻りません）。

外部キーを持つテーブルを LIKE でコピーしたところ、外部キーの列に付いていたインデックスは残り、外部キー制約そのものはコピーされませんでした。 必要な場合は ALTER TABLE … ADD CONSTRAINT … FOREIGN KEY で付け直します。

CTASと同時にキーを定義する

MySQL では、CTAS の列定義の位置にキーを書くと、コピーと同時に主キーなどを付けられます。

CREATE TABLE items_tmp (PRIMARY KEY (id), UNIQUE KEY (code))
AS SELECT * FROM items;

SHOW CREATE TABLE で、PRIMARY KEY (id) と UNIQUE KEY code (code) が付いたことを確認しました。この場合も AUTO_INCREMENT は付かないため、採番が必要なら後述の ALTER で追加します。

MySQLではSELECT INTOでテーブルを作れない

SQL Server や PostgreSQL の SELECT … INTO 新テーブル を MySQL で実行すると、次のエラーになりました。

ERROR 1327 (42000): Undeclared variable: items_into

MySQL の SELECT … INTO は変数やファイルへ値を入れるための構文で、テーブル作成には CTAS を使います。

PostgreSQLでテーブルをコピーする

CREATE TABLE AS SELECT と SELECT INTO（データごと）

CREATE TABLE items_ctas AS SELECT * FROM items;
\d items_ctas

実行結果（PostgreSQL 16.15）：

                         Table "public.items_ctas"
   Column   |            Type             | Collation | Nullable | Default
------------+-----------------------------+-----------+----------+---------
 id         | integer                     |           |          |
 code       | character varying(20)       |           |          |
 name       | character varying(50)       |           |          |
 price      | integer                     |           |          |
 status     | character varying(10)       |           |          |
 created_at | timestamp without time zone |           |          |

Nullable と Default の欄がすべて空で、Indexes や Check constraints の表示もありません。 PostgreSQL の CTAS は、列名と型とデータだけをコピーします。SELECT * INTO items_into FROM items; でも同じ定義（3行のデータ、制約なし）になりました。

この状態で INSERT すると、id も price も NULL のまま入ります。

 id | code |  name  | price | status
----+------+--------+-------+--------
  1 | A001 | pen    |   120 | active
  2 | A002 | note   |   300 | active
  3 | A003 | eraser |    80 | active
    | A001 | dup    |       |
(4 rows)

構造のみコピーする3つの書き方

-- 1) CTAS でデータを入れない
CREATE TABLE items_nodata AS SELECT * FROM items WITH NO DATA;

-- 2) 条件が必ず偽になる WHERE を付ける（どのDBでも使える書き方）
CREATE TABLE items_empty AS SELECT * FROM items WHERE 1=0;

-- 3) LIKE で定義をコピーする
CREATE TABLE items_like (LIKE items);
CREATE TABLE items_like_all (LIKE items INCLUDING ALL);

1 と 2 はどちらも 0 行のテーブルができ、定義は CTAS と同じ（制約なし）です。3 の LIKE は、INCLUDING の指定で引き継ぐ範囲が変わります。

指定なしの (LIKE items) の結果：

                         Table "public.items_like"
   Column   |            Type             | Collation | Nullable | Default
------------+-----------------------------+-----------+----------+---------
 id         | integer                     |           | not null |
 code       | character varying(20)       |           | not null |
 name       | character varying(50)       |           | not null |
 price      | integer                     |           | not null |
 status     | character varying(10)       |           |          |
 created_at | timestamp without time zone |           |          |

NOT NULL だけが引き継がれ、デフォルト値・主キー・インデックスはありません。INCLUDING ALL を付けた結果は次のとおりです。

                                 Table "public.items_like_all"
   Column   |            Type             | Collation | Nullable |           Default
------------+-----------------------------+-----------+----------+------------------------------
 id         | integer                     |           | not null | generated always as identity
 code       | character varying(20)       |           | not null |
 name       | character varying(50)       |           | not null |
 price      | integer                     |           | not null | 0
 status     | character varying(10)       |           |          | 'active'::character varying
 created_at | timestamp without time zone |           |          | CURRENT_TIMESTAMP
Indexes:
    "items_like_all_pkey" PRIMARY KEY, btree (id)
    "items_like_all_code_key" UNIQUE CONSTRAINT, btree (code)
    "items_like_all_name_idx" btree (name)
Check constraints:
    "chk_price" CHECK (price >= 0)

主キー・UNIQUE・インデックス・CHECK・デフォルト値・自動採番（IDENTITY）がすべて引き継がれました。 インデックス名は新しいテーブル名に合わせて付け直されています（idx_items_name → items_like_all_name_idx）。外部キーは INCLUDING ALL でもコピーされないことを、別のテーブルで確認しました。

LIKEで作ったテーブルへデータを入れるときの2つのポイント

id が GENERATED ALWAYS AS IDENTITY（常に自動採番）の列だと、そのまま INSERT SELECT するとエラーになります。

INSERT INTO items_like_all SELECT * FROM items;

ERROR:  cannot insert a non-DEFAULT value into column "id"
DETAIL:  Column "id" is an identity column defined as GENERATED ALWAYS.
HINT:  Use OVERRIDING SYSTEM VALUE to override.

ヒントのとおり OVERRIDING SYSTEM VALUE を付けると、元の id のままコピーできます。

INSERT INTO items_like_all OVERRIDING SYSTEM VALUE SELECT * FROM items;

もう1つのポイントは採番の開始位置です。コピー先の採番は 1 から始まるため、id が 1〜3 のデータを入れた直後に新しい行を追加すると、番号がぶつかります。

INSERT INTO items_like_all (code,name) VALUES ('A004','clip');

ERROR:  duplicate key value violates unique constraint "items_like_all_pkey"
DETAIL:  Key (id)=(1) already exists.

setval で採番の現在値をデータの最大値に合わせると解決します。

SELECT setval(pg_get_serial_sequence('items_like_all','id'),
              (SELECT MAX(id) FROM items_like_all));

INSERT INTO items_like_all (code,name) VALUES ('A004','clip');
SELECT id, code, name, price, status FROM items_like_all ORDER BY id;

 id | code |  name  | price | status
----+------+--------+-------+--------
  1 | A001 | pen    |   120 | active
  2 | A002 | note   |   300 | active
  3 | A003 | eraser |    80 | active
  4 | A004 | clip   |     0 | active
(4 rows)

SQLiteでテーブルをコピーする

CREATE TABLE items_ctas AS SELECT * FROM items;
.schema items_ctas

実行結果（SQLite 3.45.1）：

CREATE TABLE items_ctas(
  id INT,
  code TEXT,
  name TEXT,
  price INT,
  status TEXT,
  created_at TEXT
);

制約はすべてなくなり、型も INTEGER が INT と表示されるなど簡略化されました。SQLite では SELECT * INTO … は構文エラーになり、ALTER TABLE … ADD PRIMARY KEY も構文エラー（near "PRIMARY": syntax error）でした。主キーや NOT NULL を後から足す方法がないため、SQLite で制約ごと複製するには「元の CREATE 文で新しいテーブルを作ってから INSERT SELECT」という手順にします。

-- 1) 元テーブルの CREATE 文を取り出す
SELECT sql FROM sqlite_master WHERE name = 'items';

-- 2) テーブル名だけ変えて実行する
CREATE TABLE items_copy (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  code TEXT NOT NULL UNIQUE,
  name TEXT NOT NULL,
  price INTEGER NOT NULL DEFAULT 0,
  status TEXT DEFAULT 'active',
  created_at TEXT DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT chk_price CHECK (price >= 0)
);

-- 3) データを入れる
INSERT INTO items_copy SELECT * FROM items;

この手順のあとで1行追加すると、id は続きの 4、price はデフォルトの 0、status は 'active' になることを確認しました。UNIQUE と通常のインデックスだけなら、CTAS のあとに CREATE UNIQUE INDEX / CREATE INDEX で追加できます（こちらも実行して確認）。

INSERT SELECTで既存テーブルへ追記する

INSERT INTO … SELECT は、すでにあるテーブルへ、別のテーブルの行を追加する書き方です。新しいテーブルは作られないので、追記先の制約やインデックスはそのまま使われます。

-- 全列をそのまま追記（列の数と順番が同じとき）
INSERT INTO dst SELECT * FROM src;

-- 列を指定して追記（おすすめ。列の順番が違っても安全）
INSERT INTO dst (code, name, price)
SELECT code, name, price FROM src WHERE price >= 100;

列を指定する書き方には、次の利点があります。

• 列の順番や数が違うテーブルどうしでもコピーできる
• 自動採番の列（id）を外せば、追記先で新しい番号が振られる
• 指定しなかった列には、追記先のデフォルト値が入る

実際に、制約ごと作ったテーブル（PostgreSQL の items_like_all、MySQL の items_like）では、列を指定した INSERT で id が続きから振られ、price と status にデフォルト値が入りました。一方、CTAS の WHERE 1=0 で作った空テーブルに同じ INSERT をすると、PostgreSQL では id と status が NULL のまま、MySQL では id がすべて 0 になりました。「構造のみ」を CTAS で作ると、自動採番やデフォルト値がないテーブルになる点が、LIKE との大きな違いです。

消えた制約を付け直すSQL

CTAS でコピーしたテーブルを、元と同じ定義に戻すSQLです。どちらも今回実行して、元テーブルと同じ定義になることを確認しました。重複データや NULL が入っていると制約を付けられないため、先に取り除いてから実行します。

MySQL 8.0

ALTER TABLE items_ctas
  MODIFY id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  ADD UNIQUE KEY code (code),
  ADD INDEX idx_items_name (name),
  ADD CONSTRAINT chk_price_ctas CHECK (price >= 0);

実行後の SHOW CREATE TABLE には AUTO_INCREMENT=4 と表示され、次の INSERT で id=4 が振られました。MySQL では AUTO_INCREMENT の開始位置が、データの最大値の次に自動で合わせられます。

PostgreSQL 16

ALTER TABLE items_ctas
  ALTER COLUMN id SET NOT NULL,
  ALTER COLUMN code SET NOT NULL,
  ALTER COLUMN name SET NOT NULL,
  ALTER COLUMN price SET NOT NULL,
  ALTER COLUMN price SET DEFAULT 0,
  ALTER COLUMN status SET DEFAULT 'active',
  ALTER COLUMN created_at SET DEFAULT CURRENT_TIMESTAMP,
  ADD PRIMARY KEY (id),
  ADD CONSTRAINT items_ctas_code_key UNIQUE (code),
  ADD CONSTRAINT chk_price_ctas CHECK (price >= 0);

-- 自動採番を付け、開始位置をデータの最大値に合わせる
ALTER TABLE items_ctas ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY;
SELECT setval(pg_get_serial_sequence('items_ctas','id'),
              (SELECT MAX(id) FROM items_ctas));

CREATE INDEX idx_items_ctas_name ON items_ctas(name);

実行後の \d items_ctas で、主キー・UNIQUE・インデックス・CHECK・デフォルト値・IDENTITY がそろい、次の INSERT で id=4、price=0、status='active' が入ることを確認しました。

付け直すものが多いときは、最初から CREATE TABLE … (LIKE … INCLUDING ALL) と INSERT … SELECT の2段階にするほうが、書く量も確認する量も少なくて済みます。

付け直しのチェック表

目的別のおすすめの方法

CTAS は、条件や列を絞ったコピーや集計結果の保存にも使えます。

-- 一部の列と行だけコピー（MySQL 8.0.46 で確認：2行がコピーされた）
CREATE TABLE items_backup_20261003 AS
SELECT id, code, name, price FROM items WHERE price >= 100;

-- 集計結果を保存（MySQL 8.0.46・PostgreSQL 16.15 で確認）
CREATE TABLE items_sum AS
SELECT status, COUNT(*) AS cnt, SUM(price) AS total
FROM items GROUP BY status;

集計結果を保存した場合、列の型はDBが決めます。total の型は、MySQL 8.0.46 では decimal(32,0)、PostgreSQL 16.15 では bigint になりました。型をそろえたいときは CAST(SUM(price) AS …) で明示します。

コピーでつまずいたときのチェックリスト

なお、行数の多いテーブルをコピーするときは、処理時間とディスクの空き容量を先に見積もっておくと落ち着いて作業できます。まず WHERE で少量だけコピーして所要時間を確かめるのがおすすめです。

よくある質問

Q. CREATE TABLE AS SELECTで主キーやインデックスはコピーされますか？ A. コピーされません。MySQL 8.0.46・PostgreSQL 16.15・SQLite 3.45.1 のすべてで、主キー・UNIQUE・インデックス・CHECK・自動採番は引き継がれないことを確認しました。MySQL は NOT NULL とデフォルト値だけ残ります。制約ごと複製したいときは LIKE を使います。

Q. 構造だけコピーするには、WHERE 1=0 と LIKE のどちらがよいですか？ A. 制約やインデックスも必要なら LIKE（PostgreSQL は INCLUDING ALL を付ける）、列の並びだけあればよいなら WHERE 1=0 です。WHERE 1=0 はどのDBでも使える一方、主キーや自動採番のないテーブルになります。

Q. CREATE TABLE AS SELECT と INSERT SELECT の違いは何ですか？ A. CTAS は新しいテーブルを作ってデータを入れます。INSERT SELECT は既存のテーブルにデータを追加します。同じ名前のテーブルがすでにあると CTAS はエラーになるので、2回目以降の取り込みには INSERT SELECT を使います。

Q. コピーしたテーブルで自動採番が効きません。 A. CTAS では自動採番の設定が引き継がれないためです。MySQL は ALTER TABLE … MODIFY id INT NOT NULL AUTO_INCREMENT PRIMARY KEY、PostgreSQL は ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY と setval で付け直せます。

まとめ

• データごとコピーは CREATE TABLE 新 AS SELECT * FROM 元（SQL Server は SELECT * INTO 新 FROM 元）
• CTAS では主キー・UNIQUE・インデックス・CHECK・自動採番が引き継がれない（3つのDBで確認）
• MySQL の CTAS は NOT NULL とデフォルト値を残し、PostgreSQL と SQLite の CTAS は残さない
• 制約ごと複製するなら、MySQL は CREATE TABLE 新 LIKE 元、PostgreSQL は (LIKE 元 INCLUDING ALL) のあと INSERT SELECT
• 外部キーは LIKE でもコピーされないので、必要なら ALTER TABLE で付け直す
• 既存テーブルへの追記は INSERT INTO … SELECT。列を指定すると採番とデフォルト値が働く
• PostgreSQL でデータを入れたあとは、setval で採番の位置を最大値に合わせる

株式会社RJCでは、テーブルコピーの書き方と制約の扱いを仲間と学び合っています。こうした知識を共有しながら、日々技術を磨いています。

株式会社RJC ― SI事業・SES事業・AI駆動開発。RJCは一緒に成長を楽しめる会社です。