Oracle 批量插入（insert all into）

小灰灰 2022-05-22 03:52 152阅读 0赞

在`mysql`中，批量插入可以这么写：

insert into my_table(field_1,field_2)
    values
    (value_1,value_2),
    (value_1,value_2),
    (value_1,value_2);

oracle中不支持这么写，那在`Oracle`中，怎么通过一个insert语句批量插入数据呢？

INSERT ALL 
    INTO my_table(field_1,field_2) VALUES (value_1,value_2) 
    INTO my_table(field_1,field_2) VALUES (value_3,value_4) 
    INTO my_table(field_1,field_2) VALUES (value_5,value_6)
    SELECT 1 FROM DUAL;

--------------------

补充：**评论里提到的为什么要加 `SELECT 1 FROM DUAL`？**  
**官方例子：**

INSERT ALL
       INTO sales (prod_id, cust_id, time_id, amount)
       VALUES (product_id, customer_id, weekly_start_date, sales_sun)
       INTO sales (prod_id, cust_id, time_id, amount)
       VALUES (product_id, customer_id, weekly_start_date+1, sales_mon)
       INTO sales (prod_id, cust_id, time_id, amount)
       VALUES (product_id, customer_id, weekly_start_date+2, sales_tue)
       INTO sales (prod_id, cust_id, time_id, amount)
    SELECT product_id, customer_id, weekly_start_date, sales_sun,
       sales_mon, sales_tue, sales_wed, sales_thu, sales_fri, sales_sat
       FROM sales_input_table;

**个人理解：**

> “ALL into\_clause: Specify ALL followed by multiple insert\_into\_clauses to perform an unconditional multitable insert. Oracle Database executes each insert\_into\_clause once for each row returned by the subquery.”

insert all into并不表示一个表中插入多条记录，而是表示多表插入各一条记录，而这多表可以是同一个表，就成了单表插入多条记录。根据后面子查询的结果，前面每条into语句执行一次，博客正文中value都是“字面量”，所以用select 1 from dual返回一条记录即可。

参考地址：[https://docs.oracle.com/database/121/SQLRF/statements\_9015.htm\#SQLRF01604][https_docs.oracle.com_database_121_SQLRF_statements_9015.htm_SQLRF01604]  
**不当之处请指正，谢谢！**

[https_docs.oracle.com_database_121_SQLRF_statements_9015.htm_SQLRF01604]: https://docs.oracle.com/database/121/SQLRF/statements_9015.htm#SQLRF01604