Multi-table insert statement

Multi-table insert statement
INSERT statements for multiple tables are classified 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(10),    job VARCHAR2(9),    deptno NUMBER(2));INSERT INTO emp VALUES(7500, 'A', 'SALESMAN', 10);INSERT INTO emp VALUES(7501, 'B', 'MANAGER', 20);INSERT INTO emp VALUES(7502, 'C', 'CLERK', 30);INSERT INTO emp VALUES(7503, 'D', 'CLERK', 10);INSERT INTO emp VALUES(7504, 'E', 'SALESMAN', 20);INSERT INTO emp 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 execution result of the query statement is as follows: 1. The unconditional INSERT SQL code is as follows:

INSERT ALL    INTO emp1(empno, ename, job) VALUES(empno, ename, job)    INTO emp2(empno, ename, deptno) VALUES(empno, ename, 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 no conditions are added, data is inserted to both tables at the same time, and the number of data inserted in the two tables is the same. 2. the conditional insert all SQL code is as follows:

DELETE emp1;DELETE emp2;INSERT ALL    WHEN job IN ('SALESMAN', 'MANAGER') THEN    INTO emp1(empno, ename, job) VALUES(empno, ename, job)    WHEN deptno IN (10, 20) 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: when conditions are added, the statement is inserted according to the conditions. For example, EMPNO = 7500 and other data are included in both tables. Iii. the conditional insert first SQL code is as follows:

DELETE emp1;DELETE emp2;INSERT FIRST    WHEN job IN ('SALESMAN', 'MANAGER') THEN    INTO emp1(empno, ename, job) VALUES(empno, ename, job)    WHEN deptno IN (10, 20) 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, when the FIRST table meets the conditions, the second table will no longer INSERT the corresponding row, the table emp2 no longer contains the same data as the table emp1, which is the difference between insert first and insert all. 4. Transpose INSERT transpose INSERT is not so much a classification as "insert all. Create a test table:

CREATE TABLE t1 (    c1 VARCHAR2(10),    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)    INTO t1(c1, c2) VALUES('2', d2)    INTO t1(c1, c2) VALUES('3', d3)SELECT d1, d2, d3 FROM t2;SELECT * FROM t1;

The execution result of the query statement is as follows: we can see that the essence of INSERT is to INSERT data in different columns into different rows in the same table.