9 Replies Latest reply: Nov 24, 2012 1:23 AM by stratmo RSS

    Inserting data from multiple tables

    kun_87
      Hi Al,
      I want to insert data from multiple tables having same structure, into a single table.
      Can i implement "Execute immediate" ..?
        • 1. Re: Inserting data from multiple tables
          Frank Kulash
          Hi,
          kun_87 wrote:
          Hi Al,
          I want to insert data from multiple tables having same structure, into a single table.
          Can i implement "Execute immediate" ..?
          Sure, but it might be simpler, faster and more robust, just to issue a separate INSERT statement for each table.

          Why do you have multiple tables with the same structure? Why not have one big table, with perhaps an additional column? If you're using Oracle Enterprise Edition, you can partition the big table on that column, so the performance will be about the same as having smaller separate tables when you're only dealing with one of them.
          Absent Enterprise Edition, you can still have one big table. Performance might be poorer for some operations, but it will be faster for others, including the one you're asking about now.

          Arrays of similar tables usually indicate a bad table design. If you explain what you need to do, then someone can help you find a good way to do it.
          • 2. Re: Inserting data from multiple tables
            Solomon Yakobson
            kun_87 wrote:
            Can i implement "Execute immediate" ..?
            You could, but why? If target table already exists:
            INSERT
              INTO  target_table
               SELECT  *
                 FROM  table1
              UNION ALL
               SELECT  *
                 FROM  table2
            .
            .
            .
            /
            If target table doesn't exist:
            CREATE TABLE target_table
              AS
                 SELECT  *
                   FROM  table1
                UNION ALL
                 SELECT  *
                   FROM  table2
            .
            .
            .
            /
            SY.
            • 3. Re: Inserting data from multiple tables
              kun_87
              Thanks for your response, but actually i want it to implement it in a single cursor.
              I have all the other multiple tables of same name, such that i can select it from "dba_tables."
              Just want to explore on this as to how we can execute with one go.
              Hope this clarifies you.
              • 4. Re: Inserting data from multiple tables
                kun_87
                Your implications are all right, but i just want to have a knowledge whether can we execute in a single plsql block.!!!
                Like selecting all the tables from "dba_tables" as all the tables have the same name but with different signature while creating.
                Can i implement this in one go?
                • 5. Re: Inserting data from multiple tables
                  Frank Kulash
                  Hi,
                  kun_87 wrote:
                  Your implications are all right, but i just want to have a knowledge whether can we execute in a single plsql block.!!!
                  Like selecting all the tables from "dba_tables" as all the tables have the same name but with different signature while creating.
                  Can i implement this in one go?
                  Yes, you can do that, if you want to.
                  If you have trouble, post a complete test script that people can run to re-create the problem and test their ideas. Include your PL/SQL attempt, as well as CREATE TABLE and INSERT statments for all the tables involved as they exist before the INSERT, and also post the contents of the changed table after the INSERT. For testing, it's probably ehough just to have 2 or 3 source tables; we'll find a solution that works well for any number of tables.
                  • 6. Re: Inserting data from multiple tables
                    kun_87
                    Here is the dump script:
                    declare
                    sql_stmt varchar2(1000 byte);
                    cursor c1 is select table_name from dba_tables where table_name like 'AP%'
                    and tablespace_name like 'APPS%';
                    begin
                    for c2 in c1
                    loop
                    sql_stmt:='c2.tablename';
                    execute immediate '(insert into <target table> select * from||sql_stmt)';'
                    commit;
                    end loop;
                    exception
                    when others then
                    dbms_output_put_line(sqlerrm);
                    end;
                    /

                    From the above block, i am not able to implement "execute immediate" portion.!!!
                    Kindly suggest.
                    • 7. Re: Inserting data from multiple tables
                      Satyaki_De
                      As already fellow members are saying, certainly you can do that.

                      But, for that you have to ask the question. Why?

                      Exactly, why do you need that? Does it really worth?

                      If it really essential, then I would rather prefer separate tables to store those information & pass them dynamically so that only my required configurable information will be executed only. No other table won't consider in this case.
                      Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 
                      Connected as scott
                      
                      satyaki>> 
                      satyaki>> set timi on
                      satyaki>> 
                      satyaki>> 
                      satyaki>> --------------------------------------------------------
                      satyaki>> --  DDL for Table STMT_TAB
                      satyaki>> --------------------------------------------------------
                      satyaki>> CREATE TABLE STMT_TAB
                             2     (     SR_NO NUMBER,
                             3       STMT_DET VARCHAR2(500),
                             4       TABLE_NM VARCHAR2(30)
                             5     ) ;
                      
                      Table created
                      
                      Executed in 0.515 seconds
                      
                      satyaki>> create table dup_emp1
                             2  as
                             3    select * from emp
                             4    where 1=2;
                      
                      Table created
                      
                      Executed in 0.688 seconds
                      
                      satyaki>> create table dup_emp2
                             2  as
                             3    select * from dup_emp1
                             4    where 1=2;
                      
                      Table created
                      
                      Executed in 0.687 seconds
                      
                      satyaki>> select count(*) from dup_emp1;
                      
                        COUNT(*)
                      ----------
                               0
                      
                      Executed in 0.922 seconds
                      
                      satyaki>> select count(*) from dup_emp2;
                      
                        COUNT(*)
                      ----------
                               0
                      
                      Executed in 0.594 seconds
                      
                      satyaki>> 
                      
                      satyaki>> ---------------------------------------------------
                      satyaki>> --   DATA FOR TABLE STMT_TAB
                      satyaki>> --   FILTER = none used
                      satyaki>> ---------------------------------------------------
                      satyaki>> REM INSERTING into BPLOG.STMT_TAB
                      satyaki>> Insert into STMT_TAB (SR_NO,STMT_DET,TABLE_NM) values (1,'insert into ','dup_emp1');
                      
                      1 row inserted
                      
                      Executed in 0.407 seconds
                      
                      satyaki>> Insert into STMT_TAB (SR_NO,STMT_DET,TABLE_NM) values (2,'insert into ','dup_emp2');
                      
                      1 row inserted
                      
                      Executed in 0.422 seconds
                      
                      satyaki>> commit;
                      
                      Commit complete
                      
                      Executed in 0.406 seconds
                      
                      satyaki>> ---------------------------------------------------
                      satyaki>> --   Anonymous Block
                      satyaki>> ---------------------------------------------------
                      satyaki>> declare
                             2  
                             3      cursor c1
                             4      is
                             5        select STMT_DET||TABLE_NM||' select * from emp' src
                             6        from STMT_TAB;
                             7      r1 c1%rowtype;
                             8      s_err varchar2(500);
                             9  begin
                            10    for r1 in c1
                            11    loop
                            12      execute immediate r1.src;
                            13    end loop;
                            14    commit;
                            15  exception
                            16    when others then
                            17      s_err:= substr(sqlerrm,1,500);
                            18      dbms_output.put_line(s_err);
                            19  end;
                            20  /
                      
                      PL/SQL procedure successfully completed
                      
                      Executed in 0.437 seconds
                      
                      satyaki>> select count(*) from dup_emp1;
                      
                        COUNT(*)
                      ----------
                              14
                      
                      Executed in 0.641 seconds
                      
                      satyaki>> select count(*) from dup_emp2;
                      
                        COUNT(*)
                      ----------
                              14
                      
                      Executed in 0.594 seconds
                      
                      satyaki>> select count(*) from emp;
                      
                        COUNT(*)
                      ----------
                              14
                      
                      Executed in 0.609 seconds
                      
                      satyaki>> 
                      Using dba_tables may accidentally invoke those tables which might not require.

                      Frankly speaking, I won't go for dynamic approach in this case. ;)

                      Regards.

                      Satyaki De.
                      • 8. Re: Inserting data from multiple tables
                        Frank Kulash
                        Hi,
                        kun_87 wrote:
                        Here is the dump script:
                        Never write, let alone post, unformatted code. Indent PL/SQL code to show the extent of code blocks, such as DECLARE, BEGIN and LOOP. Use \
                         tags to preserve spacing when posting on this site.  See the forum FAQ {message:id=9360002}
                        
                        declare
                        sql_stmt varchar2(1000 byte);
                        cursor c1 is select table_name from dba_tables where table_name like 'AP%'
                        and tablespace_name like 'APPS%';
                        begin
                        for c2 in c1
                        loop
                        sql_stmt:='c2.tablename';
                        Because of the single-quotes, that's the 12-character string 'c2.tablename'.  If you want to use the tablename column from the c2 record (which probably has a value like 'AP_NOV_2012'), then don't use single-quotes.
                        execute immediate '(insert into <target table> select * from||sql_stmt)';'
                        Single quotes always occur in pairs (unless you're using Q-notation).  You have an odd number of single-quotes in the statement above, so there's definitely a syntax error. When writing dynamic SQL, put the entire statement into a variable so you can display it easily while debugging. For example:
                        ...
                             sql_stmt := '(insert into target_table select * from '
                                  || c2.tablename
                                  || ')';
                             dbms_output.put_line ( sql_stmt
                                       || ' = sql_stmt right before EXECUTE IMMEDIATE'
                                       );
                        --     EXECUTE IMMEDIATE sql_stmt;
                        When it looks right, then un-comment the EXECUTE IMMEDIATE statement.
                        If it causes a run-time error, then you can copy the exact code that put_line displayed, and debug that.  In this case, would
                        (insert into target_table select * from AP_NOV_2012)
                        work?  Do you always enclose INSERT statements in parentheses?
                        
                        commit;
                        end loop;
                        exception
                        when others then
                        dbms_output_put_line(sqlerrm);
                        end;
                        /
                        That EXCEPTION section isn't doing anything except hide useful information about the error.  Don't use an EXCEPTION section unless you need to. You still haven't posted any sample data, so I can't test anything. Edited by: Frank Kulash on Nov 23, 2012 1:29 PM Added comment about literal 'c2.tablename'.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
                        • 9. Re: Inserting data from multiple tables
                          stratmo
                          Hi Kun,

                          do you have direct access (not via a role) on all those table you select from. If not this may lead to problems regarding to your use of dynamic sql.
                          I try to describe two ways of solving your problem. The second one seems for me to be the better one using the package DBMS_ERRLOG-package.
                          -- these are only for a better output
                          set sqlprompt ''
                          set sqlnumber off
                          set serveroutput on
                          -- I reduce my example to two tables
                          
                          -- SOURCE
                          -- l. example: leading to PK-violation
                          drop table emp_source_1;
                          
                          Table dropped.
                          
                          drop table emp_source_2;
                          
                          Table dropped.
                          
                          
                          create table emp_source_1
                          as
                          select *
                          from HR.employees;
                          
                          Table created.
                          
                          
                          desc emp_source_1
                           Name                                      Null?    Type
                           ----------------------------------------- -------- ----------------------------
                           EMPLOYEE_ID                                        NUMBER(6)
                           FIRST_NAME                                         VARCHAR2(20)
                           LAST_NAME                                 NOT NULL VARCHAR2(25)
                           EMAIL                                     NOT NULL VARCHAR2(25)
                           PHONE_NUMBER                                       VARCHAR2(20)
                           HIRE_DATE                                 NOT NULL DATE
                           JOB_ID                                    NOT NULL VARCHAR2(10)
                           SALARY                                             NUMBER(8,2)
                           COMMISSION_PCT                                     NUMBER(2,2)
                           MANAGER_ID                                         NUMBER(6)
                           DEPARTMENT_ID                                      NUMBER(4)
                          
                          
                          alter table emp_source_1 add constraint emp_source_1_pk
                          primary key (employee_id);
                          
                          Table altered.
                          
                          
                          create table emp_source_2
                          as
                          select *
                          from HR.employees;
                          
                          Table created.
                          
                          
                          alter table emp_source_2 add constraint emp_source_2_pk
                          primary key (employee_id);
                          
                          Table altered.
                          
                          
                          /*
                          select count(*) from emp_source_1;
                          select count(*) from emp_source_2;
                          */
                          
                          -- TARGET
                          -- creating empty target-table same structure
                          drop table emp_target;
                          
                          Table dropped.
                          
                          create table emp_target
                          as select *
                          from hr.employees
                          where 1=0;
                          
                          Table created.
                          
                          
                          alter table emp_target add constraint emp_target_pk
                          primary key (employee_id);
                          
                          Table altered.
                          
                          
                          create or replace
                          procedure inserttest
                          as
                             lc_stmnt       varchar2(4000 char)
                                            := 'insert into emp_target select * from <<Tabname>>';
                          
                             lc_act_statement  varchar2(4000);
                          begin
                          
                             for tabs
                             in
                             (
                                select owner ||'.'|| table_name as tabname
                                from all_tables
                                where table_name like 'EMP%'
                             )
                             loop
                                lc_act_statement := replace(lc_stmnt, '<<Tabname>>', tabs.tabname);
                                execute immediate lc_act_statement;
                          
                             end loop;
                          exception
                             when others then
                             dbms_output.put_line(lc_act_statement);
                             dbms_output.put_line(sqlerrm);
                          end;
                          /
                          
                          Procedure created.
                          
                          
                          begin
                             inserttest;
                          end;
                          /
                          insert into emp_target select * from OTN.EMP_TARGET                             
                          ORA-00001: unique constraint (OTN.EMP_TARGET_PK) violated                       
                          
                          PL/SQL procedure successfully completed.
                          
                          
                          
                          -- 2.example using dbms_errlog
                          -- Create the error logging table.
                          drop table err$_emp_target;
                          
                          Table dropped.
                          
                          
                          BEGIN
                            DBMS_ERRLOG.create_error_log (dml_table_name => 'EMP_TARGET');
                          END;
                          /
                          
                          PL/SQL procedure successfully completed.
                          
                          
                          -- created error-log-table
                          desc err$_emp_target
                           Name                                      Null?    Type
                           ----------------------------------------- -------- ----------------------------
                           ORA_ERR_NUMBER$                                    NUMBER
                           ORA_ERR_MESG$                                      VARCHAR2(2000)
                           ORA_ERR_ROWID$                                     ROWID
                           ORA_ERR_OPTYP$                                     VARCHAR2(2)
                           ORA_ERR_TAG$                                       VARCHAR2(2000)
                           EMPLOYEE_ID                                        VARCHAR2(4000)
                           FIRST_NAME                                         VARCHAR2(4000)
                           LAST_NAME                                          VARCHAR2(4000)
                           EMAIL                                              VARCHAR2(4000)
                           PHONE_NUMBER                                       VARCHAR2(4000)
                           HIRE_DATE                                          VARCHAR2(4000)
                           JOB_ID                                             VARCHAR2(4000)
                           SALARY                                             VARCHAR2(4000)
                           COMMISSION_PCT                                     VARCHAR2(4000)
                           MANAGER_ID                                         VARCHAR2(4000)
                           DEPARTMENT_ID                                      VARCHAR2(4000)
                          
                          
                          -- slightly different procedure insertest
                          create or replace procedure inserttest
                          as
                             lc_stmnt       varchar2(4000 char)
                                            := 'insert into emp_target select * from <<Tabname>> '
                                               -- the next line is added to the insert statement
                                               ||'LOG ERRORS INTO err$_emp_target (''INSERT'') REJECT LIMIT UNLIMITED';
                          
                             lc_act_statement  varchar2(4000);
                          begin
                          
                             for tabs
                             in
                             (
                                select owner ||'.'|| table_name as tabname
                                from all_tables
                                where table_name like 'EMP%'
                             )
                             loop
                                lc_act_statement := replace(lc_stmnt, '<<Tabname>>', tabs.tabname);
                                execute immediate lc_act_statement;
                          
                             end loop;
                          exception
                             when others then
                             dbms_output.put_line(lc_act_statement);
                             dbms_output.put_line(sqlerrm);
                          end;
                          /
                          
                          Procedure created.
                          
                          
                          begin
                             inserttest;
                          end;
                          /
                          
                          PL/SQL procedure successfully completed.
                          
                          
                          -- in the end you may lookup the errors in the error-log-table you created; only
                          -- the first error per row will be reported
                          -- select * from err$_emp_target
                          -- where rownum < 11;
                          
                          
                          spool off
                          Just a last thought. It's not really safe to rely on a name-filter "table_name like 'AP%'".
                          Can you really ensure, that someone doesn't at sometime will implement a table which differs in
                          structure but with a starting "AP" in the table-name.

                          Bye

                          stratmo

                          Edited by: stratmo on Nov 24, 2012 8:21 AM position of code-tag corrected