受講料が最大70%OFF 受講料が最大70%OFF

SQLのinsert selectの構文、各DBMSごとの使用例や独自拡張についてまとめています。

SQLのinsert selectの構文

insert~select文は、既存のテーブルからデータを取得し別テーブルにinsertします。構文は以下の通りです。

INSERT INTO テーブル名1 (カラム名,カラム名…)
  SELECT カラム名,カラム名…
  FROM テーブル名2 WHERE 抽出条件

以下は特定の行を抽出して、同じテーブルに別のキーでinsertする例です。テーブルemployees(社員)のemp_no(社員番号)が10001のデータを取得して、emp_noのみ3に置き換えてemployeesにinsertしています。

insert employees 
SELECT 
	3, 
	`birth_date`, 
	`first_name`, 
	`last_name`, 
	`gender`, 
	`hire_date` 
FROM 
	`employees` 
WHERE 
	emp_no = 10001

MySQLで実行するとこうなります。

Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from employees limit 10;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
|      3 | 1953-09-02 | Georgi     | Facello   | M      | 1986-06-26 |
|  10001 | 1953-09-02 | Georgi     | Facello   | M      | 1986-06-26 |
|  10002 | 1964-06-02 | Bezalel    | Simmel    | F      | 1985-11-21 |
|  10003 | 1959-12-03 | Parto      | Bamford   | M      | 1986-08-28 |
|  10004 | 1954-05-01 | Chirstian  | Koblick   | M      | 1986-12-01 |
|  10005 | 1955-01-21 | Kyoichi    | Maliniak  | M      | 1989-09-12 |
|  10006 | 1953-04-20 | Anneke     | Preusig   | F      | 1989-06-02 |
|  10007 | 1957-05-23 | Tzvetan    | Zielinski | F      | 1989-02-10 |
|  10008 | 1958-02-19 | Saniya     | Kalloufi  | M      | 1994-09-15 |
|  10009 | 1952-04-19 | Sumant     | Peac      | F      | 1985-02-18 |
+--------+------------+------------+-----------+--------+------------+

emp_no=10001のレコードが、emp_noのみ3に置き換わってinsertされています。

【関連記事】
【SQL】SELECTした結果を別テーブルへINSERTする方法 

DBMSごとのinsert~selectの構文

Oracleのinsert~select構文

以下は、Oracleのinset~selectの例です。

INSERT INTO bonuses
   SELECT employee_id, salary*1.1 
   FROM employees
   WHERE commission_pct > 0.25; 

Oracleでリモートデータベースへの挿入を行うには、テーブル名に@を指定します。簡易的なレプリケーションが可能です。

INSERT INTO employees@remote
   VALUES (8002, 'Juan', 'Fernandez', 'juanf@example.com', NULL, 
   TO_DATE('04-OCT-1992', 'DD-MON-YYYY'), 'SH_CLERK', 3000, 
   NULL, 121, 20); 

参考)Oracle公式ドキュメント:INSERT

SQL Serverのinsert~select構文

SQL Serverのinsert~selectの例です。

INSERT INTO dbo.EmployeeSales  
    SELECT 'SELECT', sp.BusinessEntityID, c.LastName, sp.SalesYTD   
    FROM Sales.SalesPerson AS sp  
    INNER JOIN Person.Person AS c  
        ON sp.BusinessEntityID = c.BusinessEntityID  
    WHERE sp.BusinessEntityID LIKE '2%'  
    ORDER BY sp.BusinessEntityID, c.LastName;  
GO  

関連)INSERT (Transact-SQL) – SQL Server | Microsoft Docs

複数のリモートデータベースにinsert~selectでデータをコピーする場合、サブクエリのselect文でorder byを明示しないと、不整合が起こる可能性があります。大きなデータを扱う場合には注意しましょう。

MySQLのinsert~select構文

以下の例では、tbl_temp1のデータのうち、fid_order_idが100以上のデータのorder_idをtbl_temp2のfid_idにinsertします。

INSERT INTO tbl_temp2 (fld_id)
  SELECT tbl_temp1.fld_order_id
  FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100;

INSERT IGNORE INTO…とIGNOREのIGNORE指定をすると、insert時の重複が無視されます。ただし、重複が解消されるわけではないのでデータベースとしては整合性が取れない状態になります。

参考)MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.2.5.1 INSERT … SELECT 構文

PostgreSQLのinsert~select構文

INSERT INTO films SELECT * FROM tmp_films WHERE date_prod < '2004-05-07';

WITHを指定するとinsert文で使用することのできるサブクエリを記述することが可能です。

WITH upd AS (
  UPDATE employees SET sales_count = sales_count + 1 WHERE id =
    (SELECT sales_person FROM accounts WHERE name = 'Acme Corporation')
    RETURNING *
)
INSERT INTO employees_log SELECT *, current_timestamp FROM upd;

PostgreSQL公式ドキュメント:INSERT

WITHは、PostgreSQLの独自拡張です。

まとめ

ポテパンダの一言メモ
  • insert selectは、既存テーブルの内容を別テーブルに挿入するSQL
  • insert先をリモートデータベースに指定することで、簡易的なレプリケーションが可能
  • IGNORE指定で重複データが無視されるが、重複が解消されるわけではない

エンジニアになりたい人に選ばれるプログラミングスクール「ポテパンキャンプ 」

ポテパンキャンプは卒業生の多くがWebエンジニアとして活躍している実践型プログラミングスクールです。 1000名以上が受講しており、その多くが上場企業、ベンチャー企業のWebエンジニアとして活躍しています。

基礎的な学習だけで満足せず、実際にプログラミングを覚えて実践で使えるレベルまで学習したいという方に人気です。 プログラミングを学習し実践で使うには様々な要素が必要です。

それがマルっと詰まっているポテパンキャンプでプログラミングを学習してみませんか?

卒業生の多くがWebエンジニアとして活躍

卒業生の多くがWeb企業で活躍しております。
実践的なカリキュラムをこなしているからこそ現場でも戦力となっております。
活躍する卒業生のインタビューもございますので是非御覧ください。

経験豊富なエンジニア陣が直接指導

実践的なカリキュラムと経験豊富なエンジニアが直接指導にあたります。
有名企業のエンジニアも多数在籍し品質高いWebアプリケーションを作れるようサポートします。

満足度高くコスパの高いプログラミングスクール「ポテパンキャンプ」

運営する株式会社ポテパンは10,000人以上のエンジニアのキャリアサポートを行ってきております。
そのノウハウを活かして実践的なカリキュラムを随時アップデートしております。

代表の宮崎もプログラミングを覚えサイトを作りポテパンを創業しました。
本気でプログラミングを身につけたいという方にコスパ良く受講していただきたいと思っておりますので、気になる方はぜひスクール詳細をのぞいてくださいませ。