4 Replies Latest reply: Dec 14, 2012 12:22 AM by user575115 RSS

    Insert All

    user575115
      Hi,
      I'm inserting multiple tables using below sql.In this sql i need to insert one table(cw_elements_meta_reqs) two time with differ of few columns like below.But here unique constraint throwing error due sequence will reruns same vale .Is there any chance(logic) to resolve this.I have tried one time sequnce in side values and one time in select stement but not works.Pls help
      INSERT ALL
      INTO ibis.cw_portfolio_template
        (
          portfolio_template_id,
          YEAR,
          MONTH,
          group_number,
          sub_group,
          subject,
          subject_option,
          lvl,
          component,
          self_taught,
          combined
          --exam_timezone
        )
        VALUES
        (
          ibis.cw_portfolio_template_seq.nextval,
          YEAR,
          MONTH,
          group_number,
          subgroup_number,
          subject,
          subject_option,
          lvl,
          component,
          'N',
          combined
          --exam_timezone
        )
      INTO ibis.cw_portfolio_template_element
        (
          portfolio_template_element_id,
          portfolio_template_id,
          max_file_size,
          media_type_id
        )
        VALUES
        (
          ibis.cw_pf_template_element_seq.nextval,
          ibis.cw_portfolio_template_seq.nextval ,
          max_bytes,
          media_type_id
        )
      INTO ibis.cw_elements_meta_reqs
        (
          elements_meta_reqs_id,
          portfolio_template_element_id,
          element_metadata_id,
          sort_order
        )
        VALUES
        (
          ibis.cw_elements_meta_reqs_seq.nextval,--seq,
          ibis.cw_pf_template_element_seq.nextval,
          5,1
        )
      INTO ibis.cw_elements_meta_reqs
        (
          elements_meta_reqs_id,
          portfolio_template_element_id,
          element_metadata_id,
          sort_order
        )
        VALUES
        (
          ibis.cw_elements_meta_reqs_seq.nextval,
          ibis.cw_pf_template_element_seq.nextval,
          6,2
        )
      INTO ibis.cw_pf_template_element_media
        (
          PORTFOLIO_TEMPLATE_ELEMENT_ID,
          Media_type_id
        )
        VALUES
        (
          ibis.cw_pf_template_element_seq.nextval,
          media_type_id
        )
      SELECT DISTINCT YEAR,
        MONTH ,
        (SELECT sg.group_number
        FROM subject_group sg
        WHERE sg.year = ceft.year
        AND sg.month  = ceft.month
        AND sg.lvl    = ceft.lvl
        AND sg.subject= ceft.subject
        ) group_number,
        (SELECT sg.subgroup_number
        FROM subject_group sg
        WHERE sg.year = ceft.year
        AND sg.month  = ceft.month
        AND sg.lvl    = ceft.lvl
        AND sg.subject= ceft.subject
        ) subgroup_number,
        subject ,
        subject_option ,
        lvl ,
        component ,
        (SELECT ep.combined combined
        FROM ibis.subject_component sc,
          ibis.examination_paper ep
        WHERE sc.year        = ceft.year
        AND sc.month         = ceft.month
        AND sc.subject       = ceft.subject
        AND sc.subject_option= ceft.subject_option
        AND sc.lvl           = ceft.lvl
        AND sc.language      = ceft.language
        AND sc.component     =ceft.component
        AND ep.year          = sc.year
        AND ep.month         =sc.month
        AND ep.paper_code    =sc.paper_code
        ) combined,
        -- ceft.exam_timezone exam_timezone,
        max_bytes ,
        (SELECT media_type_id
        FROM ibis.cw_file_type
        WHERE file_extension=UPPER(ceft.file_ext)
        ) media_type_id
      FROM ibis.component_e_crsework_file_type ceft ;
        • 1. Re: Insert All
          jeneesh
          Like this?
          create table m(id number,name varchar2(10));
          
          create table d1(id number primary key,name varchar2(10));
          
          create sequence seq_test;
          
          insert into m values(1,'Test');
          insert into m values(2,'Test2');
          
          
          create or replace function f1 return number
          is
          begin
           return seq_test.nextval;
          end;
          
          insert all
            into d1 (id, name)
              values(seq1,name)
            into d1 (id,name)
              values(seq2,name)
          select f1 seq1,
                 f1 seq2,name
          from m;
          
          4 rows inserted.
          
          select * from d1;
          
          ID NAME     
          -- ----------
           1 Test       
           3 Test2      
           2 Test       
           4 Test2 
          • 2. Re: Insert All
            Hoek
            Can you post the exact error message?

            Keep in mind:
            "The sequence.NEXTVAL expression is used in each VALUES clause but each increment is constant for the entire INTO list. Given the fact that Oracle doesn't guarantee the execution order of an INSERT ALL statement, this sequence behaviour actually makes sense (because we couldn't guarantee a NEXTVAL before a CURRVAL, for example)."

            http://www.oracle-developer.net/display.php?id=209
            (Scroll down to the "multi-table inserts and sequences"-part )

            And you might want to explore this thread as well:
            http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:6915127515933#tom7871897954847
            Testing with nextval and currval might do it.
            • 3. Re: Insert All
              user575115
              thanks.
              • 4. Re: Insert All
                user575115
                .