Oracle 批量插入sql

oracle9i版本以后

1 无条件的 INSERT ALL

insert all  
    into test1 (empno,ename) values(empno,ename)  
    into test1(ename,sal) values(ename,sal)  
    select 1 from dual; 

 注意后边跟的SELECT语句可以随意，不过不是把它SELECT出来的内容插入前边的表里，而是起到前边的多个数据每次插入多少行的作用，这个多少行是由后边跟的SELECT语句查出来几条而定的，如后边的跟的SELECT 语句查出了15条记录，则前边的

"INSERT ALL INTO  a表 VALUES(各个值1) 

INTO a表 VALUES (其它值2)

INTO a表 VALUES(其它值3)"

就会先插入值1对应的各个字段插入15条记录，然后插入值2各个对应的字段15条记录，然后插入值3对应的各个字段15条记录。插入的值中不能有主键，且应该有自增主键。

批量插入多个VALUES这样的一条记录，所以后边的SELECT 语句只要能查出一条记录就行，建议用SELECT 1 FROM DUAL。

下面的方法同上面的语句：

INSERT INTO CM_NOTICE_CONTROL_SETTINGS  (OPERATE_USERID, NOTICE_SENDING_FLAG ,CREATORID)

      SELECT '11','YYYYYY','11' FROM DUAL

      UNION ALL SELECT '22','YYYYY','22' FROM DUAL

      UNION ALL SELECT '33','YYYYY','33' FROM DUAL

2 有条件的INSERT ALL语句

(对于每一行数据，对每一个when条件都进行检查，如果满足条件就执行插入操作)

   -- 有条件的INSERT ALL语句  
    insert all  
    when sal>1000 then  
    into test1 (empno,sal,mgr) values (empno,sal,mgr)   
    when comm<500 then  
    into test1 (empno,comm,mgr) values(empno,comm,mgr)  
    select empno,sal,mgr,comm   
    from emp;  

3 有条件的 FIRST INSERT语句

(对于每一行数据，只插入到第一个when条件成立的表，不继续检查其他条件)

-- 有条件的 FIRST INSERT语句  
-- 对于每一行数据，只插入到第一个when条件成立的表，不继续检查其他条件。  
insert first  
    when sal>1000 then  
    into test1 (empno,sal,mgr) values (empno,sal,mgr)   
    when comm<500 then  
    into test1 (empno,comm,mgr) values(empno,comm,mgr)  
    select empno,sal,mgr,comm   
    from emp;  

INSERT ALL 和INSERT FIRST对比

SQL> -- 有条件的INSERT ALL语句  
 insert all  
      when sal>1000 then  
     into test1 (empno,sal,mgr) values (empno,sal,mgr)  
     when comm<500 then  
     into test1 (empno,comm,mgr) values(empno,comm,mgr)  
     select empno,sal,mgr,comm  
     from emp;  
  
已创建14行。  
  
SQL> -- 有条件的 FIRST INSERT语句  
SQL> -- 对于每一行数据，只插入到第一个when条件成立的表，不继续检查其他条件。  
 insert first  
     when sal>1000 then  
     into test1 (empno,sal,mgr) values (empno,sal,mgr)  
     when comm<500 then  
     into test1 (empno,comm,mgr) values(empno,comm,mgr)  
     select empno,sal,mgr,comm  
     from emp;  
  
已创建12行。  

4 旋转INSERT (行转列插入）

对查出的每条数据都进行插入操作

-- 旋转INSERT (行转列插入)  
insert all    
    into sales_info values(employee_id,week_id,sales_mon)    
    into sales_info values(employee_id,week_id,sales_tue)    
    into sales_info values(employee_id,week_id,sales_wed)    
    into sales_info values(employee_id,week_id,sales_thur)    
    into sales_info values(employee_id,week_id,sales_fri)    
    select employee_id,week_id,sales_mon,sales_tue,    
    sales_wed,sales_thur,sales_fri 
    from sales_source_data; 

效果：

--原表  
select * from sales_source_data;    
EMPLOYEE_ID    WEEK_ID  SALES_MON  SALES_TUE  SALES_WED SALES_THUR  SALES_FRI    
----------- ---------- ---------- ---------- ---------- ---------- ----------    
        176          6       2000       3000       4000       5000       6000    
-- 转存后的表  
select * from sales_info;    
EMPLOYEE_ID       WEEK      SALES    
----------- ---------- ----------    
        176          6       2000    
        176          6       3000    
        176          6       4000    
        176          6       5000    
        176          6       6000 

 例子：

insert all
 
into CM_NOTICE_CONTROL_SETTINGS (OPERATE_USERID, NOTICE_SENDING_FLAG ,CREATORID) values(USERID,'YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY',USERID)
 
select distinct USERID from CM_LOGINUSERINFO where USERID is not null;

//取前5条记录插入
 
insert all
 
into CM_NOTICE_CONTROL_SETTINGS (OPERATE_USERID, NOTICE_SENDING_FLAG ,CREATORID)
 
values(USERID,'YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY',USERID)
 
select *
 
from (
 
     select distinct USERID
 
     from CM_LOGINUSERINFO
 
     where USERID is not null
 
     )
 
where rownum<=5;

//取6-10条记录插入
 
insert all
 
into CM_NOTICE_CONTROL_SETTINGS (OPERATE_USERID, NOTICE_SENDING_FLAG ,CREATORID)
 
values(USERID,'YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY',USERID)
 
select USERID
 
from(
 
  select rownum as no,USERID
 
  from (
 
       select distinct USERID
 
       from CM_LOGINUSERINFO
 
       where USERID is not null
 
       )
 
  where rownum<=10
 
   )
 
where no>=6 and no<=10;