【SQL】使用一条INSERT语句完成多表插入

2020-02-15 08:15:39

这是一条颠覆常规的插入方法，一条INSERT语句可以完成向多张表的插入任务。小小地展示一下这种插入方法。

1.创建表T并初始化测试数据，此表作为数据源。
sec@ora10g> create table t (x number(10), y varchar2(10));
sec@ora10g> insert into t values (1,'a');
sec@ora10g> insert into t values (2,'b');
sec@ora10g> insert into t values (3,'c');
sec@ora10g> insert into t values (4,'d');
sec@ora10g> insert into t values (5,'e');
sec@ora10g> insert into t values (6,'f');
sec@ora10g> commit;

2.查看表T的数据
sec@ora10g> select * from t;

         X Y
---------- ----------
         1 a
         2 b
         3 c
         4 d
         5 e
         6 f

6 rows selected.

3.创建表T1和T2，作为我们要插入的目标表。
sec@ora10g> create table t1 as select * from t where 0=1;

Table created.

sec@ora10g> create table t2 as select * from t where 0=1;

Table created.

4.种多表插入方法INSERT ALL
1）完成INSERT ALL插入
sec@ora10g> insert all into t1 into t2 select * from t;

12 rows created.

这里之所以显示插入了12条数据，实际上表示在T1表中插入了6条，T2表插入了6条，一共是12条数据。

2）验证T1表中被插入的数据。
sec@ora10g> select * from t1;

         X Y
---------- ----------
         1 a
         2 b
         3 c
         4 d
         5 e
         6 f

6 rows selected.

3）验证T2表中被插入的数据。
sec@ora10g> select * from t2;

         X Y
---------- ----------
         1 a
         2 b
         3 c
         4 d
         5 e
         6 f

6 rows selected.

OK，完成INSERT ALL命令的使命。

5.第二种多表插入方法INSERT FIRST
1）清空表T1和T2
sec@ora10g> delete from t1;
sec@ora10g> delete from t2;
sec@ora10g> commit;

2）完成INSERT FIRST插入
sec@ora10g> insert first when x>=5 then into t1 when x>=2 then into t2 select * from t;

5 rows created.

处理逻辑是这样的，首先检索T表查找X列值大于等于5的数据（这里是“5，e”和“6，f”）插入到T1表，然后将前一个查询中出现的数据排除后再查找T表，找到X列值大于等于2的数据再插入到T2表（这里是“2，b”、“3，c”和“4，d”）。注意INSERT FIRST的真正目的是将同样的数据只插入一次。
3）验证T1表中被插入的数据。
sec@ora10g> select * from t1;

         X Y
---------- ----------
         5 e
         6 f

4）验证T2表中被插入的数据。
sec@ora10g> select * from t2;

         X Y
---------- ----------
         2 b
         3 c
         4 d

5)为真实的反映“数据只插入一次”的目的，我们把条件颠倒后再插入一次。
sec@ora10g> delete from t1;
sec@ora10g> delete from t2;

sec@ora10g> insert first when x>=2 then into t1 when x>=5 then into t2 select * from t;

5 rows created.

sec@ora10g> select * from t1;

         X Y
---------- ----------
         2 b
         3 c
         4 d
         5 e
         6 f

sec@ora10g> select * from t2;

no rows selected

OK，目的达到，可见满足第二个条件的数据已经包含在个条件里，所以不会有数据插入到第二张表。

同样的插入条件，我们把“INSERT FIRST”换成“INSERT ALL”，对比一下结果。
sec@ora10g> delete from t1;

5 rows deleted.

sec@ora10g> delete from t2;

0 rows deleted.

sec@ora10g> insert all when x>=2 then into t1 when x>=5 then into t2 select * from t;

7 rows created.

sec@ora10g> select * from t1;

         X Y
---------- ----------
         2 b
         3 c
         4 d
         5 e
         6 f

sec@ora10g> select * from t2;

         X Y
---------- ----------
         5 e
         6 f

是不是在豁然开朗的基础上又有一种锦上添花的感觉。That's it.

6.Oralce官方文档参考链接
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_9014.htm#SQLRF01604

7.小结
这些小小小的SQL技巧在实际的应用中有很大用处。慢慢体会吧。

Good luck.

secooler
10.01.06

-- The End --