【これだけ】SQLでSELECT結果をINSERTする方法【現役SEが解説】
今回は、SQLでSELECT結果をINSERTする方法について、単純に追加する方法と不足分を追加する方法に分け、簡単に解説していきます。
SELECT結果を単純に別テーブルにINSERTする方法
バックアップ用テーブルの作成等で、テーブルの中身をそのまま別のテーブルにコピーしたいことがあると思います。
このような場合には、レコードを追加するINSERT文にレコードを取得するSELECT文を入れることで実現することができます。
INSERT INTO {inserted_table_name} SELECT * FROM {inserting_table_name};
カラムを指定する場合には、INSERT文とSELECT文の両方でカラムを指定しなければいけません。
INSERT INTO {inserted_table_name} ({column_name},…) SELECT {column_name},… FROM {inserting_table_name};
当然SELECT文にはWHERE句等を使って条件で絞ったレコードのみを抽出し、INSERTすることも可能です。
別テーブルの不足分のレコードをSELECTを使ってINSERTする方法
あるテーブルから別テーブルにデータをコピーする際に、不足分だけをコピーするには、以下のSQL文で可能です。
INSERT INTO {inserted_table_name} AS A SELECT * FROM {inserting_table_name} AS B WHERE NOT EXISTS (SELECT * FROM {inserted_table_name} WHERE A.{key_column_name} = B.{key_column_name});
括弧内のSELECT文では両方のテーブルにあるものを抽出して、NOT EXISTS句で、その反対、すなわち、両方にないものだけをSELECTしてINSERTすることができます。
MySQLでAUTO_INCREMENTの欠番を防ぐことにも使える
MySQLでは、主キー以外にユニークキーを設定して重複チェックをMySQLの機能に委ねることができますが、AUTO_INCREMENTを使用している場合、欠番が出ることがあります。
結論から言うとユニークキーの設定は不要となるのですが、今回解説したWHERE句で条件を絞ってSELECTした結果をINSERTするという方法を応用することで、この問題を回避することができます。
| user_id | user_cd | name|
|---|---|---|
| 1 | 1001 | 山田太郎 |
| 2 | 1002 | 田中花子 |
| 3 | 1003 | 小林一郎 |
例えば、上記のようなusersテーブルがあり、user_idがAUTO_INCREMENTの主キーで、user_cdが主キーではないものの重複を許さないユニークキーであるとします。
この例のテーブルに、user_cdの1001のレコードを追加しようとすると、既に存在するためエラーとなってはくれますが、次回レコード追加時に主キーのuser_idが連番ではなくなってしまいます。
INSERT INTO users (user_cd, name) SELECT '1001', '山田太郎' FROM users WHERE NOT EXISTS (SELECT * FROM users WHERE user_cd = '1001');
そこで、上記のようにSELECT文を活用してINSERTさせると、ユニークキーが重複した場合でも、AUTO_INCREMENTのuser_idの連番に影響を与えることなく、欠番をなくすことができますので、とても有用なSQL文となります。