SQL insert selectの構文 リモートへの簡易レプリケーションや独自拡張

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

SQLのinsert selectの構文

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

INSERT INTO テーブル名１ (カラム名,カラム名…)
  SELECT カラム名,カラム名…
  FROM テーブル名２ 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指定で重複データが無視されるが、重複が解消されるわけではない