Use an INSERT statement to complete the multi-table insert

This is a subversion of the conventional method of insertion, an INSERT statement to complete the task of inserting multiple tables. Little showcase this insertion method.

1. Create a table T and initialize the test data, this table as a data source.

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. View the data of the table T.

sec @ ora10g> select * from t;

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

6 rows selected.

Create tables T1 and T2 as the target table we want to insert.

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.

The first multi-table INSERT INS

1) Complete INSERT ALL insert

sec @ ora10g> insert all into t1 into t2 select * from t;

12 rows created.

The reason why here is inserted into the 12 data, in fact, said in the T1 table inserted six, T2 table inserted six, a total of 12 data.

2) Verify that the data inserted in the T1 table.

sec @ ora10g> select * from t1;

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

6 rows selected.

3) Verify that the data inserted in the T2 table.

sec @ ora10g> select * from t2;

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

6 rows selected.

OK, complete the mission of the INSERT ALL command.

5. The second multi-table insert INSERT FIRST

1) Clear the tables T1 and T2

sec @ ora10g> delete from t1;
sec @ ora10g> delete from t2;
sec @ ora10g> commit;

2) INSERT FIRST insert completed

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

5 rows created.

The processing logic is like this: First, search the T table to find the data with the X column value greater than or equal to 5 (here "5, e" and "6, f") inserted into the T1 table and then exclude the data appearing in the previous query Then look up the T-table and find the data whose X-column value is greater than or equal to 2 and insert it into the T2 table (here, "2, b", "3, c" and "4, d"). Note that the real purpose of INSERT FIRST is to insert the same data only once.

3) Verify that the data inserted in the T1 table.

sec @ ora10g> select * from t1;

XY
---------- ----------
5 e
6 f

4) Verify that the data inserted in the T2 table.

sec @ ora10g> select * from t2;

XY
---------- ----------
2 b
3 c
4 d

5) For the true reflection of "data is inserted only once", we invert the condition and insert it again.

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;

XY
---------- ----------
2 b
3 c
4 d
5 e
6 f

sec @ ora10g> select * from t2;

no rows selected

OK, the purpose is achieved, we can see the data to meet the second condition already included in the first condition, so there is no data inserted into the second table.

The same insertion conditions, we put "INSERT FIRST" replaced by "INSERT ALL", compare the results.

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;

XY
---------- ----------
2 b
3 c
4 d
5 e
6 f

sec @ ora10g> select * from t2;

XY
---------- ----------
5 e
6 f

Is not based on the seemingly simple yet another feeling of icing on the cake. That's it.

6.Oralce official document reference link

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_9014.htm#SQLRF01604

7. Summary

These small and advanced SQL skills are of great use in practical applications. Slowly understand it.

Good luck.

secooler
10.01.06

- The End -