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);
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;
WITHは、PostgreSQLの独自拡張です。
まとめ
- insert selectは、既存テーブルの内容を別テーブルに挿入するSQL
- insert先をリモートデータベースに指定することで、簡易的なレプリケーションが可能
- IGNORE指定で重複データが無視されるが、重複が解消されるわけではない
