INSERT FIRST && INSERT ALL（按条件进行多表插入）

最新推荐文章于 2024-08-29 09:53:29 发布

原创最新推荐文章于 2024-08-29 09:53:29 发布 · 1k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#command

ORACLE 专栏收录该内容

3 篇文章

订阅专栏

本文介绍了INSERTFIRST和INSERTALL两种SQL插入方式的区别，并通过具体实例进行对比分析。INSERTFIRST在找到符合条件的目标表后立即停止查找并插入数据，而INSERTALL会遍历所有条件并将数据插入所有符合条件的目标表。

INSERT FIRST && INSERT ALL

两者区别：
insert first：找到条件匹配表后就直接break；不再继续查下边是否还有匹配表。
insert all ：逐一排查每个条件，插入到所有满足条件的表中去。

实例分析对比：
CREATE TABLE T1 （ID NUMBER,sal NUMBER）;
CREATE TABLE T2 AS SELECT * FROM T1;
CREATE TABLE T3 AS SELECT * FROM T1;
CREATE TABLE T4 AS SELECT * FROM T1;
CREATE TABLE T5 AS SELECT * FROM T1;

SELECT * FROM T1;
SELECT * FROM T2;
SELECT * FROM T3;
SELECT * FROM T4;
SELECT * FROM T5;

INSERT INTO T1 VALUES(1,2000);
INSERT INTO T1 VALUES(1,200);
INSERT INTO T1 VALUES(2,5000);
INSERT INTO T1 VALUES(2,500);
INSERT INTO T1 VALUES(3,15000);
INSERT INTO T1 VALUES(3,1500);
COMMIT;

INSERT FIRST
WHEN ID=1 THEN INTO T2
WHEN ID=2 THEN INTO T3
when id=3 then into T4
else into T5
SELECT ID,SUM(sal) total_sal FROM T1
GROUP BY ID;
COMMIT;
--->
T1
ID SAL
1 1 2000
2 1 200
3 2 5000
4 2 500
5 3 15000
6 3 1500
T2
ID SAL
1 1 2200
T3
ID SAL
1 2 5500
T4
ID SAL
1 3 16500
T5
ID SAL

INSERT ALL
WHEN ID=1 THEN INTO T2
WHEN ID=2 THEN INTO T3
when id=3 then into T4
else into T5
SELECT ID,SUM(sal) total_sal FROM T1
GROUP BY ID;
COMMIT;
--->
T1 T2 T3 T4 T5 同 INSERT FIRST

INSERT ALL
WHEN ID=1 THEN INTO T2
WHEN ID=2 THEN INTO T3
else into T4
SELECT ID,SUM(sal) total_sal FROM T1
GROUP BY ID;
COMMIT;
--->
T1 T2 T3 T4 T5 同 INSERT FIRST

INSERT FIRST
WHEN ID=1 THEN INTO T2
WHEN ID=1 THEN INTO T5
WHEN ID=2 THEN INTO T3
when id=3 then into T4
SELECT ID,SUM(sal) total_sal FROM T1
GROUP BY ID;
COMMIT;
--->
T1 T2 T3 T4 T5 同 INSERT FIRST

DELETE FROM t2;
DELETE FROM t3;
DELETE FROM t4;
DELETE FROM t5;

COMMIT;

考题：

1. You need to load information about new customers from the NEW_CUST table into the tables CUST
and CUST_SPECIAL. If a new customer has acredit limit greater than 10,000, then the details havetobe
inserted into CUST_SPECIAL. All new customer details have to be inserted into the CUST table.Which
technique should be used to load the data most efficiently?
A. external table
B. the MERGE command
C. the multitable INSERT command
D. INSERT using WITH CHECK OPTION
Answer: C

1。你需要从NEW_CUST表中加载了新的客户信息到表CUST和CUST_SPECIAL。如果一个新的客户的 acredit 额度大于10000，那么插入数据到CUST_SPECIAL。

所有新客户的详细信息都必须插入到CUST 表中.下面哪种技术最有效地加载数据？

A.外部表
B.MERGE命令
C.在多表INSERT命令

D. INSERT using WITH CHECK OPTION

答案：C