Oracle inserts multiple tables (insert All/first)

Source: Internet
Author: User

1 , build a test table

[C-sharp]view plaincopyprint?
  1. CREATE TABLE Edw_int
  2. (
  3. Agmt_no VARCHAR2 (+ BYTE) not NULL,
  4. Agmt_sub_no VARCHAR2 (4 BYTE) not NULL,
  5. Need_repay_int number (22,2),
  6. Curr_period Number (4) Not NULL
  7. );
  8. CREATE TABLE edw_int_1
  9. (
  10. Agmt_no VARCHAR2 (+ BYTE) not NULL,
  11. Agmt_sub_no VARCHAR2 (4 BYTE) not NULL,
  12. Need_repay_int number (22,2),
  13. Curr_period Number (4) Not NULL
  14. );
  15. CREATE TABLE edw_int_2
  16. (
  17. Agmt_no VARCHAR2 (+ BYTE) not NULL,
  18. Agmt_sub_no VARCHAR2 (4 BYTE) not NULL,
  19. Need_repay_int number (22,2),
  20. Curr_period Number (4) Not NULL
  21. );

2 , inserting test data

[C-sharp]view plaincopyprint?
  1. INSERT into Edw_int
  2. (Agmt_no, Agmt_sub_no, Need_repay_int, Curr_period)
  3. VALUES
  4. (' 20003874 ', ' 2104 ', 3126.5, 7);
  5. INSERT into Edw_int
  6. (Agmt_no, Agmt_sub_no, Need_repay_int, Curr_period)
  7. VALUES
  8. (' 20003874 ', ' 2104 ', 3290.76, 6);
  9. INSERT into Edw_int
  10. (Agmt_no, Agmt_sub_no, Need_repay_int, Curr_period)
  11. VALUES
  12. (' 20003874 ', ' 2104 ', 3454.06, 5);
  13. INSERT into Edw_int
  14. (Agmt_no, Agmt_sub_no, Need_repay_int, Curr_period)
  15. VALUES
  16. (' 20003874 ', ' 2104 ', 3616.41, 4);
  17. INSERT into Edw_int
  18. (Agmt_no, Agmt_sub_no, Need_repay_int, Curr_period)
  19. VALUES
  20. (' 20017143 ', ' 2104 ', 2350.86, 0);
  21. INSERT into Edw_int
  22. (Agmt_no, Agmt_sub_no, Need_repay_int, Curr_period)
  23. VALUES
  24. (' 20017143 ', ' 2104 ', 3566.55, 0);
  25. INSERT into Edw_int
  26. (Agmt_no, Agmt_sub_no, Need_repay_int, Curr_period)
  27. VALUES
  28. (' 20018273 ', ' 2104 ', 1639.46, 0);
  29. INSERT into Edw_int
  30. (Agmt_no, Agmt_sub_no, Need_repay_int, Curr_period)
  31. VALUES
  32. (' 20018273 ', ' 2104 ', 2080.49, 0);
  33. 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?
  1. 1. Build a line multi-list and test data insertion
  2. CREATE TABLE Week_bal (ID int,w1_bal number,w2_bal number,w3_bal number,w4_bal number,w5_bal number);
  3. INSERT into Week_bal values (1,10.09,12.98,23.89,89.08,1098.01);
  4. Commit
  5. SELECT * from Week_bal;
  6. 2, build a small number of rows of the table
  7. CREATE TABLE week_bal_new (ID int,week int,bal number);
  8. 3. Insert with INSERT all statement
  9. Insert All
  10. Into Week_bal_new (Id,week,bal) VALUES (Id,1,w1_bal)
  11. Into Week_bal_new (Id,week,bal) VALUES (Id,2,w2_bal)
  12. Into Week_bal_new (Id,week,bal) VALUES (Id,3,w3_bal)
  13. Into Week_bal_new (Id,week,bal) VALUES (Id,4,w4_bal)
  14. Into Week_bal_new (Id,week,bal) VALUES (Id,5,w5_bal)
  15. Select Id,w1_bal,w2_bal,w3_bal,w4_bal,w5_bal from Week_bal;
  16. SELECT * from Week_bal_new;

Oracle inserts multiple tables (insert All/first)

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.