写Oracle的人十有八九都写过INSERT INTO ... SELECT但很多人把它当“复制粘贴”用从来没想过这条语句能玩出多少花活。第22期我就把INSERT INTO和SELECT联用的用法从头到尾捋一遍。这篇文章不只是列语法我会把什么时候用、为什么这么写、性能怎么提、坑在哪里都讲清楚适合刚接触Oracle的初学者也适合写了好几年SQL但没系统整理过这类写法的开发。1. INSERT INTO和SELECT联用到底是什么场景下的需求1.1 你会在什么时候想用这条语句先说个最典型的场景你有一张订单表orders里面存了三年数据总共几千万行。业务方突然说要把去年之前的订单单独归档到orders_archive前端查询只留今年的数据。你当然可以先把旧数据SELECT出来再一条条INSERT到归档表但傻子才这么干。INSERT INTO ... SELECT就是把“查出数据”和“写入目标表”合并成一条SQL数据库内部直接流转不用经过应用层速度快得多代码也干净。类似的场景还有把A表的部分字段合并到B表、把汇总结果写入统计报表、把接口数据经过加工后落到临时表、给开发环境造一批测试数据。说白了只要目标表存在、数据源是某个查询结果INSERT INTO ... SELECT就是最直接的工具。1.2 基本语法拆解列对应关系才是核心很多初学者背语法只背个大概写成INSERT INTO t1 SELECT * FROM t2结果有一天t2表加了字段脚本当场报错或者数据串列。核心规则就一句话SELECT出来的列按顺序对应INSERT INTO后面括号里的列。INSERT INTO target_table (col1, col2, col3) SELECT source_col1, source_col2, source_col3 FROM source_table WHERE 过滤条件;这里有两个要点。第一目标表括号里的列和SELECT的列不是按名字匹配是按位置匹配所以写的时候一定数清楚数量、对清楚类型。第二INSERT INTO后面可以不写列名直接写INSERT INTO target_table SELECT ...这个写法要求SELECT查出来的列数量、顺序和类型和目标表完全一致非常脆弱我建议一律不要用除非你只是临时倒一次数据。我见过一个真实案例某同事用INSERT INTO t1 SELECT * FROM t2做每日同步结果上游t2表中间加了一列数据直接错位数字填到了日期字段里跑了好几天才发现。从那之后我给自己定了个规矩任何INSERT...SELECT都显式写列名哪怕目标表只有两个字段也写全。这不是多打几个字的问题是给自己留一条命。2. 五种高频用法直接对着抄2.1 全表复制和条件复制最简单的用法把一张表的数据完整复制到另一张结构相同的表INSERT INTO orders_copy SELECT * FROM orders;如果只要部分数据加WHEREINSERT INTO orders_archive SELECT * FROM orders WHERE order_date DATE 2024-01-01;这里要注意目标表是否已经存在。如果目标表不存在要用CREATE TABLE AS SELECT后面会专门讲如果目标表存在但为空上面写法没问题如果目标表里已经有数据INSERT是追加不会清空原有数据。很多人忘了这一点跑了两遍脚本数据翻倍还以为是Bug。想要“先清后插”通常的做法是TRUNCATE TABLE orders_archive; INSERT INTO orders_archive SELECT * FROM orders WHERE order_date DATE 2024-01-01;TRUNCATE是DDL不能回滚执行前确认目标表就是你要清空的那张。在这类场景里TRUNCATE比DELETE快得多因为它不生成大量undo日志但代价就是不可恢复所以生产环境用之前要谨慎确认。2.2 跨表列替换与数据加工实际业务中很少直接原样复制更多是从几张表里取字段经过计算再落到目标表。比如建一张客户价值分层表从客户表取基本信息从订单表聚合出消费总额INSERT INTO customer_value (customer_id, customer_name, total_amount, level) SELECT c.customer_id, c.customer_name, NVL(SUM(o.order_amount), 0), CASE WHEN NVL(SUM(o.order_amount), 0) 10000 THEN A WHEN NVL(SUM(o.order_amount), 0) 5000 THEN B ELSE C END FROM customers c LEFT JOIN orders o ON o.customer_id c.customer_id WHERE c.status ACTIVE GROUP BY c.customer_id, c.customer_name;这类写法有几个容易忽略的点。一是聚合后别忘了GROUP BY而且GROUP BY的字段要和非聚合SELECT字段保持一致二是LEFT JOIN时如果子表没有匹配记录SUM会返回NULL要习惯性用NVL包一层三是这种INSERT通常会消耗较多临时表空间数据量大时建议先查一下执行计划。数据加工还有一个常见需求去重后插入。比如从流水表取出每个用户最近一条记录写入用户最新状态表可以用ROW_NUMBER()INSERT INTO user_latest_status (user_id, status, update_time) SELECT user_id, status, update_time FROM ( SELECT user_id, status, update_time, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY update_time DESC) rn FROM user_status_log ) t WHERE rn 1;用子查询包一层再过滤rn 1这个模式很好用Oracle的ROW_NUMBER()窗口函数在这种场景下比GROUP BY更灵活因为它能保留完整行数据而不是只保留聚合列。2.3 INSERT ALL一次插入多张表INSERT ALL是我最喜欢的特性之一很多人不知道或者知道但用得少。它的作用是一条SQL把同一份查询结果同时插入多张目标表。先说一个典型的坑分表归档。比如订单数据要按照年份拆到orders_2022、orders_2023、orders_2024三张表常规思路是写三条INSERTINSERT INTO orders_2022 SELECT * FROM orders WHERE order_date DATE 2022-01-01 AND order_date DATE 2023-01-01; INSERT INTO orders_2023 SELECT * FROM orders WHERE order_date DATE 2023-01-01 AND order_date DATE 2024-01-01; INSERT INTO orders_2024 SELECT * FROM orders WHERE order_date DATE 2024-01-01 AND order_date DATE 2025-01-01;这样写没什么错但orders表会被扫三遍。如果orders表有上亿行每一遍全表扫描的时间都够你喝杯咖啡。用INSERT ALL可以一次扫描、按条件分发INSERT ALL WHEN order_date DATE 2022-01-01 AND order_date DATE 2023-01-01 THEN INTO orders_2022 (order_id, order_date, customer_id, amount) VALUES (order_id, order_date, customer_id, amount) WHEN order_date DATE 2023-01-01 AND order_date DATE 2024-01-01 THEN INTO orders_2023 (order_id, order_date, customer_id, amount) VALUES (order_id, order_date, customer_id, amount) WHEN order_date DATE 2024-01-01 AND order_date DATE 2025-01-01 THEN INTO orders_2024 (order_id, order_date, customer_id, amount) VALUES (order_id, order_date, customer_id, amount) SELECT order_id, order_date, customer_id, amount FROM orders;源表只访问一次数据按条件直接分发到三张目标表性能和可读性都提升了。注意INSERT ALL里的WHEN是逐条判断的每条记录可能进入多个WHEN分支也就是不互斥这一条记录如果满足两个条件会被插入两张表。想要互斥效果得用INSERT FIRST它会按顺序匹配第一个满足的分支后面的不再判断。回到上面例子如果你的日期条件写成交叉区间INSERT ALL就会重复插入所以要么保证条件严格不重叠要么换INSERT FIRST。这是INSERT ALL和INSERT FIRST最本质的区别很多人踩坑就踩在没搞懂这个。2.4 用SELECT代替VALUES造测试数据写存储过程或者做功能测试的时候经常需要往临时表里灌一批测试数据。用INSERT INTO ... VALUES一条条写写几十条能累死。这时候可以借助SELECT从任何来源构造数据包括不存在的表——你只需要利用Oracle自带的dual虚拟表。最经典的连续数字序列生成INSERT INTO seq_test (id) SELECT ROWNUM FROM dual CONNECT BY LEVEL 1000;这个写法利用层次查询CONNECT BY LEVEL 1000和ROWNUM一次性生成1到1000的连续数字。想生成日期序列包一层DATE 2024-01-01 ROWNUM - 1就好。实际测试中我经常这么干一下子造出全年每一天的记录INSERT INTO daily_sales (sale_date, sale_amount) SELECT DATE 2024-01-01 LEVEL - 1, ROUND(DBMS_RANDOM.VALUE(100, 10000), 2) FROM dual CONNECT BY LEVEL 366;用DBMS_RANDOM.VALUE生成随机金额日期从元旦开始往后推一张365天的测试表就有了。注意CONNECT BY LEVEL这种方式不要在生产环境的超大表上乱用它本质是递归生成行层级太深会消耗大量内存。测试场景下几千几万行没问题。2.5 变量拼接和动态SQL场景在PL/SQL或者存储过程里INSERT INTO ... SELECT不一定是静态SQL很多时候要拼接动态SQL来处理“表名或条件不确定”的情况。比如归档程序要把每个月的订单写入当月分区表EXECUTE IMMEDIATE INSERT INTO orders_ || to_char(v_month, YYYYMM) || SELECT * FROM orders WHERE order_date :start_date AND order_date :end_date USING v_start, v_end;动态SQL用USING传绑定变量避免直接拼接日期字符串引发类型转换问题也能减少SQL注入风险。这里有个经验动态SQL只要能用绑定变量就不要把值直接拼进字符串。这不仅是安全问题也是性能问题——绑定变量能让SQL共享游标减少硬解析对Oracle来说意义重大。还有种情况是配合BULK COLLECT做批处理。数据量大的时候逐行INSERT太慢可以先把数据收集到内存再批量插入但这属于PL/SQL优化范畴跟今天的主体有点偏我提一句想深入可以单独研究FORALL。3. 性能与日志从慢到快的调优经验3.1 为什么默认INSERT慢日志和回滚段在作怪同样一批数据INSERT INTO ... SELECT有时候执行要几分钟有时候只要几秒。差别不在数据量而在你有没有理解Oracle写入数据的底层机制。默认情况下INSERT会走Buffer Cache每插入一批数据都会生成对应的redo log重做日志和undo回滚段。redo log保证数据库崩溃时可以恢复undo保证事务可以回滚。这是关系型数据库ACID的代价你没法完全绕开。批量插入时要生成海量redo日志写入成为瓶颈所以慢。另一个拖慢性能的因素是索引。目标表上每建一个索引插入数据时就要同步维护索引条目。索引越多插得越慢。一张表上挂五六个索引插入性能能下降好几倍。所以大批量导入前通常的建议是先ALTER TABLE ... DISABLE CONSTRAINT禁用约束甚至DROP INDEX导完再重建。这个操作要评估好业务空窗期不能在生产高峰干。3.2 APPEND提示和NOLOGGING的正确打开方式要提升大批量插入性能最常用的一招是APPEND提示INSERT /* APPEND */ INTO orders_archive SELECT * FROM orders WHERE order_date DATE 2024-01-01;加了APPEND之后Oracle会使用直接路径插入Direct Path Insert数据不再经过Buffer Cache而是直接写到数据文件的新块上同时生成的redo日志会大幅减少。效果有多明显我之前在生产环境归档一张1.2亿行的表普通INSERT预计40分钟加APPEND后8分钟跑完差距就是这么大。使用APPEND有几个限制必须记住目标表的高水位线HWM以下如果有空闲块APPEND不会重用它只会往高水位线之上追加新块。如果你反复删除再插入表可能越来越大出现“删除后空间不释放”的现象。要对表做ALTER TABLE ... SHRINK SPACE或重建才能回收。APPEND是直接路径插入事务隔离级别下插入过程中其他会话不能对目标表做DML操作否则会冲突。如果目标表是分区表APPEND可以配合分区级操作但要注意分区交换等更高效的方案是否存在。如果你还想再省一点日志可以对目标表设置NOLOGGINGALTER TABLE orders_archive NOLOGGING;注意NOLOGGING不是完全不生成日志而是对某些操作比如直接路径INSERT不写redo。这种模式的代价是如果执行过程中数据库崩溃这些数据可能无法恢复你可能需要重新导入。所以NOLOGGING适合那些可以从源表重新生成的数据不适合唯一数据。生产环境用不用取决于你的数据容灾需求。做完大插入之后记得把表改回LOGGINGALTER TABLE orders_archive LOGGING;APPENDNOLOGGING是批量插入的黄金搭档但绝不能盲用。我在公司内部定了个规则归档类、可重建数据的批量导入可以用业务核心表、唯一数据表不允许用NOLOGGING除非DBA审批。3.3 并行插入的适用条件和坑数据量大到一定程度单进程插入还是慢这时候可以上并行Parallel DML。最常见的写法INSERT /* PARALLEL(4) */ INTO orders_archive SELECT * FROM orders WHERE order_date DATE 2024-01-01;PARALLEL(4)表示用4个并行进程来执行插入。并行度设多少不是越大越好。Oracle默认的并行度上限和CPU核心数、PARALLEL_MAX_SERVERS参数有关。并行度太高会导致资源争抢把其他业务的性能拖垮。我常用的经验值是并行度不超过CPU核数的两倍数据量低于几百万行就别并行并行调度本身有开销小数据量并行反而更慢。并行插入还有两个容易踩的坑第一并行DML和事务限制。并行插入执行过程中你不能在同一个事务里做其他操作否则会报ORA-12801之类的并行执行错误。一般建议把并行插入单独放在一个事务里。第二并行和APPEND是绑定关系。在不少Oracle版本里并行DML会自动启用直接路径插入也就是说你写PARALLEL提示即使不写APPEND也可能走直接路径。这意味着并行插入同样受APPEND的特性限制比如不重用高水位线以下的空闲空间。另外一个容易被忽略的点INSERT ... SELECT的并行度要生效SELECT部分也得能并行。如果源表太小优化器可能选择不用并行你得用EXPLAIN PLAN FOR看执行计划确认并行是否真的启动了。4. 和CTAS的对比什么时候别用INSERT...SELECT4.1 CTAS一条语句建表加灌数据CREATE TABLE AS SELECT简称CTAS是一条把“建表”和“灌数据”合并的语句CREATE TABLE orders_2024 AS SELECT * FROM orders WHERE order_date DATE 2024-01-01 AND order_date DATE 2025-01-01;执行完表结构和数据一并产生。相比INSERT INTO ... SELECTCTAS少了一步“先建表再插入”的过程而且天然走直接路径写入性能也很不错。所以很多人不知道该用哪个的时候会直接选CTAS。但CTAS有几个和INSERT不一样的地方需要牢记不会自动复制原表的约束、索引、触发器。CTAS只复制列的数据类型主键、外键、检查约束、默认值、注释、索引、触发器全部不会带过来。你想得到一张和生产一样的表CTAS之后还得手工补这些对象。如果CTAS失败表会处于什么状态CTAS是DDL失败时表可能创建了但数据不完整或者表被部分创建情况比较复杂。存储属性CTAS默认使用和源表一样的表空间吗不一定要看当前用户的默认表空间和TABLESPACE设置。4.2 结构、约束、权限的差异做一张对比表大家看得更清楚对比维度INSERT INTO ... SELECTCREATE TABLE AS SELECT目标表是否预先存在必须已存在无需存在自动创建是否复制约束保留目标表已有约束不复制源表约束是否复制索引目标表已有索引继续维护不会复制源表索引是否复制默认值用目标表定义不复制源表默认值是否复制注释无影响不会复制注释是否追加数据追加到已有数据之后新表只有本次数据能否控制事务可以INSERT可回滚DDL隐式提交不能回滚性能默认路径较慢可用APPEND直接路径通常较快4.3 选择建议拿上面的表我可以直接给结论目标表不存在只要数据不要约束用CTAS一条语句搞定性能也好。目标表已存在结构固定只要加数据用INSERT INTO ... SELECT。需要复制完整结构包括约束、索引、触发器先CREATE TABLE LIKEOracle用CREATE TABLE t2 AS SELECT * FROM t1 WHERE 10建空表再补约束索引最后INSERT。或者干脆用DBMS_METADATA.GET_DDL导出建表语句生成目标表后插入数据。数据量大且可重跑TRUNCATEINSERT /* APPEND */是个经典组合。分区表场景如果目标表是分区表INSERT INTO ... SELECT会走分区路由如果用CTAS重建分区结构分区定义不会复制需要手工重建。把场景想清楚选型就自然了。很多人纠结性能选CTAS还是INSERT实际上多数情况下决定权在于目标表的结构是否已存在而不是性能。5. 实操场一段完整的归档迁移案例5.1 需求描述与表结构光讲语法很枯燥我来写一个完整的小案例包含目标表建表、数据加工、数据校验你直接能抄到自己的项目里。假设我们有个电商库orders表有订单数据结构如下CREATE TABLE orders ( order_id NUMBER(12) NOT NULL, order_date DATE NOT NULL, customer_id NUMBER(10) NOT NULL, order_amount NUMBER(10,2) NOT NULL, pay_status VARCHAR2(20) DEFAULT UNPAID, create_time TIMESTAMP DEFAULT SYSTIMESTAMP );现在要把2023年之前的订单归档到orders_archive同时只保留支付成功的订单并且给归档数据打一个归档批次号方便后续追溯。5.2 三步完成迁移脚本第一步建归档表。先检查表是否存在不存在就建CREATE TABLE orders_archive AS SELECT * FROM orders WHERE 10;WHERE 10是Oracle里建一个空表结构的经典技巧只复制列结构不复制数据。然后给归档表加上需要的额外字段ALTER TABLE orders_archive ADD archive_batch_id NUMBER(10);第二步执行归档插入。归档批次号用今天日期的YYYYMMDDHH24MISS格式转成数字INSERT /* APPEND */ INTO orders_archive (order_id, order_date, customer_id, order_amount, pay_status, create_time, archive_batch_id) SELECT order_id, order_date, customer_id, order_amount, pay_status, create_time, TO_NUMBER(TO_CHAR(SYSDATE, YYYYMMDDHH24MISS)) AS archive_batch_id FROM orders WHERE order_date DATE 2023-01-01 AND pay_status PAID;这里有个细节源表有4个字段但我加了一个archive_batch_id所以目标表列名列表写了7个SELECT也对应7个其中第7个是临时算出来的批次号。第三步删除源表已归档数据。这一步务必谨慎最好先验证归档数量再删DELETE FROM orders WHERE order_date DATE 2023-01-01 AND pay_status PAID;如果整个迁移过程要在同一个事务里控制让INSERT和DELETE要么一起成功、要么一起回滚那就别在INSERT里加APPEND直接路径插入和普通事务的兼容性更麻烦而是用普通INSERTINSERT INTO orders_archive (...) SELECT ...; DELETE FROM orders WHERE ...; COMMIT;实习生最容易出的问题是INSERT没提交直接跑了DELETE结果DELETE锁住了半张表其他业务全部堵住。记得先确认INSERT影响行数再执行DELETE最后统一提交。稳妥的人还会先SELECT COUNT(*)做交叉验证。5.3 校验数据迁移完别急着盖棺定论必须做数据校验。最核心的校验是总数一致性-- 源表和归档表各维度对比 SELECT COUNT(*) AS src_count, SUM(order_amount) AS src_amount FROM orders WHERE order_date DATE 2023-01-01 AND pay_status PAID;查出来的结果和归档表对比SELECT COUNT(*) AS arch_count, SUM(order_amount) AS arch_amount FROM orders_archive WHERE archive_batch_id TO_NUMBER(TO_CHAR(SYSDATE, YYYYMMDDHH24MISS));注意如果你在归档时用的批次号是语句执行时获取的SYSDATE而校验时再取一次SYSDATE可能差了几秒导致批次号对不上归档就不会被查出来。所以建议先把批次号定义成变量存起来DEFINE batch_id TO_NUMBER(TO_CHAR(SYSDATE, YYYYMMDDHH24MISS))或者在PL/SQL块里用变量DECLARE v_batch_id NUMBER; BEGIN SELECT TO_NUMBER(TO_CHAR(SYSDATE, YYYYMMDDHH24MISS)) INTO v_batch_id FROM dual; INSERT INTO orders_archive (...) SELECT ...; DBMS_OUTPUT.PUT_LINE(v_batch_id); END;这种小细节实战里能省不少排查时间。6. 常见问题与排查技巧实录6.1 ORA-00947、ORA-00913、ORA-01722不是每个报错都能看懂最常见的几个我列个速查表报错含义常见原因解决办法ORA-00947: not enough values值不够INSERT列数多于SELECT列数核对列数量ORA-00913: too many values值太多INSERT列数少于SELECT列数核对列数量ORA-01722: invalid number无效数字字符串转数字失败多半数据里有脏数据用TO_NUMBER包一层或者先检查源数据ORA-01400: cannot insert NULL不能插入NULL非空列没给值用NVL补默认值ORA-00001: unique constraint violated主键冲突目标表已有相同主键先查重或改用MERGEORA-12801: parallel execution error并行错误并行DML使用不当检查并行度看源表是否被锁有一次同事跑导入报ORA-01722查了半天发现源表某个字段存了1,000这种带千分位的字符串TO_NUMBER转换当然失败。后来统一用TO_NUMBER(REPLACE(字段, ,, ))处理。主键冲突是批量插入最烦的问题。INSERT INTO ... SELECT不像MERGE那样自带“存在就更新”的逻辑。如果目标表已经有数据你要先查一下哪些主键会冲突SELECT src.id FROM source_table src WHERE EXISTS (SELECT 1 FROM target_table tgt WHERE tgt.id src.id);查到冲突数据后决定是跳过、覆盖还是原地更新。需要更新可以用MERGE INTO那是另一个话题了。6.2 数据倾斜和锁等待大批量插入时目标表会被锁其他会话的DML会被阻塞表现为SQL*Loader或者应用侧一直等待查询V$LOCK能看到卡住的锁。常规做法是把大批量操作放在业务低峰期或者用分区表做分区交换。比如我们生产环境就是把归档表按月份分区每个月用ALTER TABLE ... EXCHANGE PARTITION把一整月的数据秒级切换出去完全不需要长时间锁表。这是比INSERT ... SELECT更高阶的玩法如果你们有归档需求强烈建议研究一下分区交换。数据倾斜也会影响插入性能。INSERT ... SELECT如果源表数据分布严重不均某个并行进程可能处理了大部分数据导致整体时间被单点拖长。排查方法就是看V$SESSION里各会话的SQL_ID和BLOCKING_SESSION判断是不是某个进程成了瓶颈。6.3 日常避坑Checklist根据我自己的使用经验整理了一份检查清单每次写INSERT INTO ... SELECT之前过一遍[ ] 目标表和生产环境确认过吗别在测试环境把生产表TRUNCATE了[ ] 列名列表写全了吗有没有用SELECT *然后祈祷字段顺序[ ] 字段类型兼容吗日期、数字、字符串会不会隐式转换出问题[ ] 目标表索引会影响插入速度吗大表要不要先禁索引[ ] 是不是该加APPEND但加了APPEND后能不能回滚业务允许吗[ ] 数据量多大要不要开并行并行度设多少[ ] 插入后要不要校验行数有没有做COUNT对比[ ] 会不会主键冲突要不要先查重[ ] 如果脚本失败了目标表里的数据是完整还是不完整有没有做幂等设计最后一条尤其重要。好的插入脚本应该是可重入的也就是说跑两次不会出问题。怎么做到要么插入前TRUNCATE目标表要么用DELETE清理本次批次的数据再插入要么目标表主键冲突时用MERGE而不是纯INSERT。最后分享一个小技巧INSERT INTO ... SELECT用得好不好很大程度取决于你对数据状态的掌控力。我的习惯是在执行大脚本之前先把SELECT部分单独跑一遍把结果的行数、SUM值记录下来。这个预查询看起来很傻但它能让你在INSERT跑完后迅速确认数据对不对也能避免“脚本跑了半小时才发现条件写错”的尴尬。我自己踩过最狠的一次坑是把WHERE order_date DATE 2023-01-01写成了WHERE order_date DATE 2023-01-01结果倒反天罡把新数据全归档了线上数据直接消失一部分。第二天排查了一上午才定位是条件反了。这之后我给自己定了个死规矩任何涉及DELETE或TRUNCATE的脚本WHERE条件必须单独先SELECT出来看一遍源表行数、时间范围、客户数全确认过了再去做数据变更。Oracle的INSERT INTO ... SELECT说简单就是“查出来放进去”说复杂它能牵扯到日志策略、并行调度、事务隔离、锁资源、约束索引一系列底层机制。把这篇文章里的场景吃透碰到底层问题就不会慌至少知道该往哪个方向排查。至于更多进阶玩法比如MERGE、分区交换、外部表加载那都是这条主线上长出来的分支以后有机会再写。