1.单表单行INSERT
INSERT INTO table[(column1,column2,...)] VALUE(value1,value2,...)
2.无条件多表INSERT
INSERT[ALL][condition_insert_clause]
[insert_into_clause values_clause] (subquery)
例子:
insert all
into e_sal(empno,sal) values(empno,sal)
into e_job(no,job) values(empno,job)
select empno,sal,job from emp
/
3.有条件的多表INSERT
INSERT [ALL | FIRST]
WHEN condition THEN into_clause values_clause
[WHEN condition THEN] into_clause values_clause]
......
[ELSE] [insert_into_clause values_clause]
Subquery;
例子:
insert all
when salary<=1000 then into sal1000 values(emp_id,hire_date,salary)
when salary<=2000 then into sal2000 values(emp_id,hire_date,salary)
else
into sal_else values(emp_id,hire_date,salary)
select empno emp_id,hiredate hire_date,sal salary from emp
/
insert first
when salary<=1000 then into sal1000 values(emp_id,hire_date,salary)
when salary<=2000 then into sal2000 values(emp_id,hire_date,salary)
else
into sal_else values(emp_id,hire_date,salary)
select empno emp_id,hiredate hire_date,sal salary from emp
/
当使用all时,从上到下检查每个条件,满足就执行后面的into。
当使用first时,从上到下,一旦满足就执行后面的into,之后终止判断,开始下个row的检查。
4.pivoting insert,实际上就是无条件的insert all