Oracle inserts multiple tables (insert All/first)

1 , build a test table

[C-sharp]view plaincopyprint?

2. CREATE TABLE Edw_int
4. (
6. Agmt_no VARCHAR2 (+ BYTE) not NULL,
8. Agmt_sub_no VARCHAR2 (4 BYTE) not NULL,
10. Need_repay_int number (22,2),
12. Curr_period Number (4) Not NULL
14. );
16. CREATE TABLE edw_int_1
18. (
20. Agmt_no VARCHAR2 (+ BYTE) not NULL,
22. Agmt_sub_no VARCHAR2 (4 BYTE) not NULL,
24. Need_repay_int number (22,2),
26. Curr_period Number (4) Not NULL
28. );
30. CREATE TABLE edw_int_2
32. (
34. Agmt_no VARCHAR2 (+ BYTE) not NULL,
36. Agmt_sub_no VARCHAR2 (4 BYTE) not NULL,
38. Need_repay_int number (22,2),
40. Curr_period Number (4) Not NULL
42. );

2 , inserting test data

[C-sharp]view plaincopyprint?

2. INSERT into Edw_int
4. (Agmt_no, Agmt_sub_no, Need_repay_int, Curr_period)
6. VALUES
8. (' 20003874 ', ' 2104 ', 3126.5, 7);
10. INSERT into Edw_int
12. (Agmt_no, Agmt_sub_no, Need_repay_int, Curr_period)
14. VALUES
16. (' 20003874 ', ' 2104 ', 3290.76, 6);
18. INSERT into Edw_int
20. (Agmt_no, Agmt_sub_no, Need_repay_int, Curr_period)
22. VALUES
24. (' 20003874 ', ' 2104 ', 3454.06, 5);
26. INSERT into Edw_int
28. (Agmt_no, Agmt_sub_no, Need_repay_int, Curr_period)
30. VALUES
32. (' 20003874 ', ' 2104 ', 3616.41, 4);
34. INSERT into Edw_int
36. (Agmt_no, Agmt_sub_no, Need_repay_int, Curr_period)
38. VALUES
40. (' 20017143 ', ' 2104 ', 2350.86, 0);
42. INSERT into Edw_int
44. (Agmt_no, Agmt_sub_no, Need_repay_int, Curr_period)
46. VALUES
48. (' 20017143 ', ' 2104 ', 3566.55, 0);
50. INSERT into Edw_int
52. (Agmt_no, Agmt_sub_no, Need_repay_int, Curr_period)
54. VALUES
56. (' 20018273 ', ' 2104 ', 1639.46, 0);
58. INSERT into Edw_int
60. (Agmt_no, Agmt_sub_no, Need_repay_int, Curr_period)
62. VALUES
64. (' 20018273 ', ' 2104 ', 2080.49, 0);
66. COMMIT;

3 , Insert All/first Statement Example

(1) , Insert all- without conditions

[C-sharp]view plaincopyprint?

1. Insert All
2. Into Edw_int_1 (agmt_no,agmt_sub_no,need_repay_int,curr_period)
3. VALUES (Agmt_no,agmt_sub_no,need_repay_int,curr_period)
4. Into Edw_int_2 (agmt_no,agmt_sub_no,curr_period)
5. VALUES (agmt_no, ' 1234 ', curr_period)
6. Select Agmt_no,agmt_sub_no,need_repay_int,curr_period from Edw_int;
7. Commit

(2) , Insert all- with conditions

[C-sharp]view plaincopyprint?

1. Insert All
2. When Curr_period=0 Then
3. Into Edw_int_1 (agmt_no,agmt_sub_no,need_repay_int,curr_period)
4. VALUES (Agmt_no,agmt_sub_no,need_repay_int,curr_period)
5. Else
6. Into Edw_int_2 (agmt_no,agmt_sub_no,need_repay_int,curr_period)
7. VALUES (Agmt_no,agmt_sub_no,need_repay_int,curr_period)
8. Select Agmt_no,agmt_sub_no,need_repay_int,curr_period from Edw_int;
9. Commit

(3) , Insert first- with conditions

[C-sharp]view plaincopyprint?

1. Insert First
2. When Curr_period=0 Then
3. Into Edw_int_1 (agmt_no,agmt_sub_no,need_repay_int,curr_period)
4. VALUES (Agmt_no,agmt_sub_no,need_repay_int,curr_period)
5. When agmt_sub_no= ' 2104 ' then
6. Into Edw_int_2 (agmt_no,agmt_sub_no,need_repay_int,curr_period)
7. VALUES (Agmt_no,agmt_sub_no,need_repay_int,curr_period)
8. Select Agmt_no,agmt_sub_no,need_repay_int,curr_period from Edw_int;
9. Commit

4 , Data results View

[C-sharp]view plaincopyprint?

1. TRUNCATE TABLE edw_int_1;
2. TRUNCATE TABLE edw_int_2;
3. SELECT * from Edw_int;
4. SELECT * from Edw_int_1;
5. SELECT * from Edw_int_2;

5 , All with the First the Difference

All : do not consider the relationship, as long as the conditions are satisfied, all inserted;

First : consider a succession relationship if there is data that satisfies the first when condition satisfies the second one . when condition, the first one is executed Then Insert a statement, the second Then you don't insert the first one . Then the data that has been inserted.

The difference can also be described as, All repeated insertions may be made as long as the conditions are met; First The first thing to do is to satisfy the condition, then filter, not repeat the insert.

6. use Insert all to implement row and column conversions (insert all rotation function)

[C-sharp]view plaincopyprint?

2. 1. Build a line multi-list and test data insertion
4. CREATE TABLE Week_bal (ID int,w1_bal number,w2_bal number,w3_bal number,w4_bal number,w5_bal number);
6. INSERT into Week_bal values (1,10.09,12.98,23.89,89.08,1098.01);
8. Commit
10. SELECT * from Week_bal;
12. 2, build a small number of rows of the table
14. CREATE TABLE week_bal_new (ID int,week int,bal number);
16. 3. Insert with INSERT all statement
18. Insert All
20. Into Week_bal_new (Id,week,bal) VALUES (Id,1,w1_bal)
22. Into Week_bal_new (Id,week,bal) VALUES (Id,2,w2_bal)
24. Into Week_bal_new (Id,week,bal) VALUES (Id,3,w3_bal)
26. Into Week_bal_new (Id,week,bal) VALUES (Id,4,w4_bal)
28. Into Week_bal_new (Id,week,bal) VALUES (Id,5,w5_bal)
30. Select Id,w1_bal,w2_bal,w3_bal,w4_bal,w5_bal from Week_bal;
32. SELECT * from Week_bal_new;

Oracle inserts multiple tables (insert All/first)