Multiple table INSERT statement

Multiple table INSERT statements are divided into the following four types:

① unconditional insert.

② conditional Insert All.

③ transpose insert.

④ conditional Insert First.

First create a test table:

CREATE TABLE EMP (    empno Number (4),    ename VARCHAR2 (Ten),    job VARCHAR2 (9),    deptno number (2)); INSERT into EMP VALUES (7500, ' A ', ' salesman ', ten), insert into EMP values (7501, ' B ', ' MANAGER ', ' + '), insert into EMP values (7502, ' C ', ' INSERT into EMP values (7503, ' D ', ' Clerk ', ten), insert into EMP values (7504, ' E ', ' salesman ', +), insert into E MP VALUES (7505, ' F ', ' MANAGER ', 30); CREATE TABLE emp1 as SELECT empno, ename, job from emp WHERE 1 = 2; CREATE TABLE emp2 as SELECT empno, ename, deptno from emp WHERE 1 = 2; SELECT * from EMP;

The result of the query statement execution is as follows:

I. Unconditional INSERT

The SQL code is as follows:

INSERT all    to Emp1 (empno, ename, Job) VALUES (empno, ename, job) into    emp2 (empno, ename, Deptno) VALUES (empno, en Ame, Deptno) SELECT empno, ename, Job, deptno from emp WHERE deptno = 10; SELECT * from EMP1; SELECT * from EMP2;

The execution result of the first query statement is as follows:

The execution result of the second query statement is as follows:

　　

Because there are no conditions, data is inserted into two tables at the same time, and the number of bars inserted in the two tables is the same.

Two. Conditional Insert All

The SQL code is as follows:

DELETE emp1;delete Emp2;insert All when    job on (' salesman ', ' MANAGER ') then into    emp1 (empno, ename, Job) VALUES (em Pno, ename, Job) when Deptno (+) then into EMP2 (Empno, ename, Deptno) VALUES (empno, ename, Deptno    ) SELECT E Mpno, ename, Job, deptno from EMP; SELECT * from EMP1; SELECT * from EMP2;

The execution result of the first query statement is as follows:

The execution result of the second query statement is as follows:

When conditions are added, they are inserted conditionally, such as empno=7500 data in two tables.

Three. Conditional Insert First

The SQL code is as follows:

DELETE emp1;delete Emp2;insert First when    job on (' salesman ', ' MANAGER ') then into    emp1 (empno, ename, Job) VALUES (  Empno, ename, Job) when Deptno (+) then into EMP2 (Empno, ename, Deptno) VALUES (empno, ename, Deptno    ) SELECT Empno, ename, Job, deptno from EMP; SELECT * from EMP1; SELECT * from EMP2;

The execution result of the first query statement is as follows:

The execution result of the second query statement is as follows:

In the Insert first statement, the second table will no longer be inserted into the corresponding row when it meets the criteria, and table EMP2 no longer has the same data as the table EMP1, which is the difference between insert first and insert all.

Four. Transpose insert

Transpose insert is not so much a classification as a usage of "INSERT all".

To create a test table:

CREATE TABLE T1 (    C1 VARCHAR2 (Ten),    C2 VARCHAR2 (10)); CREATE TABLE T2 asselect ' A ' as D1,       ' B ' as D2,       ' C ' as D3 from  DUAL;

The SQL code is as follows:

INSERT all into    T1 (c1, C2) VALUES (' 1 ', D1) to    T1 (C1, C2) VALUES (' 2 ', D2) into    T1 (c1, C2) VALUES (' 3 ', D3) Sele CT D1, D2, D3 from T2; SELECT * from T1;

The result of the query statement execution is as follows:

As you can see, the essence of the device insert is to insert data from different columns into the non-peers of the same table.

Multiple table INSERT statement