12 Replies Latest reply: Jan 14, 2013 3:04 AM by Marwim RSS

    Loading incremantal Data ::

    983563
      I have two tables  EMP1,EMP2_HIST is a history table
      EMp1 is an OLTP table,EMP2_HIST is a history table
      daily incremental data is loaded from EMP1 to EMP2_HIST tables
      how can i do this one through PL/SQL 
      
      How can i achieve this one .Can you Please explain me on this 
        • 1. Re: Loading incremantal Data ::
          damorgan
          I'd rather explain to you why you should NOT do this.

          PL/SQL should never be used when SQL will suffice. Given that one can do the following with ease:
          INSERT INTO emp2_hist
          SELECT *
          FROM emp1
          WHERE <some_condition_is_true_to_avoid_resending_older_rows>;
          anyone wanting to use PL/SQL should be taken out to a reeducation camp.

          There are valid times and places for PL/SQL but what you have described, so far, isn't one of them.

          Though as you may be a student here's what I would turn in as a best practice PL/SQL solution (add exception handling if appropriate):
          BEGIN
            INSERT INTO emp2_hist
            SELECT *
            FROM emp1
            WHERE <some_condition_is_true_to_avoid_resending_older_rows>;
            COMMIT;
          END;
          /
          And if your instructor doesn't like this advice have him or her contact me directly. You will find my contact information here:
          http://www.morganslibrary.org/contact.html
          • 2. Re: Loading incremantal Data ::
            rp0428
            Welcome to the forum!

            Whenever you post provide your 4 digit Oracle version (result of SELECT * FROM V$VERSION)
            >
            I have two tables EMP1,EMP2_HIST is a history table
            EMp1 is an OLTP table,EMP2_HIST is a history table
            daily incremental data is loaded from EMP1 to EMP2_HIST tables
            how can i do this one through PL/SQL
            >
            Why do you need to use PL/SQL. Just use an INSERT INTO ... SELECT ... FROM query and then delete the records, if needed, from the EMP1 table.
            • 3. Re: Loading incremantal Data ::
              SomeoneElse
              how can i do this one through PL/SQL
              MERGE (?)
              • 4. Re: Loading incremantal Data ::
                Chanchal Wankhade
                Hi,

                you mean you will get daily some data and you want to upload it in test table and then compare it with you live table, if the record exist then it should update and if records is not exist then it should insert that records correct.

                Hope you have created primary keys on you live table so comparing would be easy with test table.

                for that, i think you have two ways...
                1) Use merge
                2) Cursor.

                How to use merge.
                create or replace procedure test
                as 
                begin
                MERGE
                      INTO  live_table a
                       USING test_table b
                       ON  ( a.primary_key = b.primary_key......you can specify multiple column name here. )
                    WHEN MATCHED
                    THEN
                       UPDATE
                       SET   a.column_name1 = b.column_name1,
                            a.column_name2 = b.column_name2
                   WHEN NOT MATCHED
                   THEN
                      INSERT ( a.List_of_Column_names_you_need_to_Insert)
                      VALUES ( a.List_of_Column);
                Exception ---if you catch any exception here
                end;
                cursor,
                create or replace procedure test
                as
                cursor cur is select * From test_table;
                v_1 number;
                begin
                for rec in cur
                loop
                select count(*) into v_1 from live_table a
                where  a.primary_key=rec.primary_key;
                if v_1 >0 then
                update statement;
                else
                insert statement;
                exception if you have any;
                end;
                Note:-
                Its advisable to use Merge or Clean updates statement Over Cursor For Loop.
                • 5. Re: Loading incremantal Data ::
                  Marwim
                  Its advisable to use Merge or Clean updates statement Over Cursor For Loop.
                  True, but even when you use a slow row by row cursor solution you should not test the existence before doing your DML.

                  Just try to insert. When it fails because of duplicates (you need an unique index on your match criterium) then catch the exception and update the row.

                  Regards
                  Marcus
                  • 6. Re: Loading incremantal Data ::
                    Rahul_India
                    damorgan wrote:
                    I'd rather explain to you why you should NOT do this.

                    PL/SQL should never be used when SQL will suffice. Given that one can do the following with ease:
                    INSERT INTO emp2_hist
                    SELECT *
                    FROM emp1
                    WHERE <some_condition_is_true_to_avoid_resending_older_rows>;
                    anyone wanting to use PL/SQL should be taken out to a reeducation camp.

                    There are valid times and places for PL/SQL but what you have described, so far, isn't one of them.

                    Though as you may be a student here's what I would turn in as a best practice PL/SQL solution (add exception handling if appropriate):
                    BEGIN
                    INSERT INTO emp2_hist
                    SELECT *
                    FROM emp1
                    WHERE <some_condition_is_true_to_avoid_resending_older_rows>;
                    COMMIT;
                    END;
                    /
                    And if your instructor doesn't like this advice have him or her contact me directly. You will find my contact information here:
                    http://www.morganslibrary.org/contact.html
                    morgan i take help of your website regularly.Will u reply to mails if i shoot you one ;)
                    • 7. Re: Loading incremantal Data ::
                      BluShadow
                      Rahul India wrote:
                      damorgan wrote:
                      I'd rather explain to you why you should NOT do this.

                      PL/SQL should never be used when SQL will suffice. Given that one can do the following with ease:
                      INSERT INTO emp2_hist
                      SELECT *
                      FROM emp1
                      WHERE <some_condition_is_true_to_avoid_resending_older_rows>;
                      anyone wanting to use PL/SQL should be taken out to a reeducation camp.

                      There are valid times and places for PL/SQL but what you have described, so far, isn't one of them.

                      Though as you may be a student here's what I would turn in as a best practice PL/SQL solution (add exception handling if appropriate):
                      BEGIN
                      INSERT INTO emp2_hist
                      SELECT *
                      FROM emp1
                      WHERE <some_condition_is_true_to_avoid_resending_older_rows>;
                      COMMIT;
                      END;
                      /
                      And if your instructor doesn't like this advice have him or her contact me directly. You will find my contact information here:
                      http://www.morganslibrary.org/contact.html
                      morgan i take help of your website regularly.Will u reply to mails if i shoot you one ;)
                      I would think like most, if you are just emailing him to as him for help with your own issues, he'll probably just tell you to use the forums, but if you're emailing to raise issue with something he describes on his website or in relation to any advice he's given, he'll likely respond to that. You have to remember that people here have their own jobs to do and are not offering free off-forum support for people who want to just target experts. One of the reasons I remain anonymous, I don't have time to be helping individuals on a personal level.
                      • 8. Re: Loading incremantal Data ::
                        Rahul_India
                        BluShadow wrote:
                        Rahul India wrote:
                        damorgan wrote:
                        I'd rather explain to you why you should NOT do this.

                        PL/SQL should never be used when SQL will suffice. Given that one can do the following with ease:
                        INSERT INTO emp2_hist
                        SELECT *
                        FROM emp1
                        WHERE <some_condition_is_true_to_avoid_resending_older_rows>;
                        anyone wanting to use PL/SQL should be taken out to a reeducation camp.

                        There are valid times and places for PL/SQL but what you have described, so far, isn't one of them.

                        Though as you may be a student here's what I would turn in as a best practice PL/SQL solution (add exception handling if appropriate):
                        BEGIN
                        INSERT INTO emp2_hist
                        SELECT *
                        FROM emp1
                        WHERE <some_condition_is_true_to_avoid_resending_older_rows>;
                        COMMIT;
                        END;
                        /
                        And if your instructor doesn't like this advice have him or her contact me directly. You will find my contact information here:
                        http://www.morganslibrary.org/contact.html
                        morgan i take help of your website regularly.Will u reply to mails if i shoot you one ;)
                        I would think like most, if you are just emailing him to as him for help with your own issues, he'll probably just tell you to use the forums, but if you're emailing to raise issue with something he describes on his website or in relation to any advice he's given, he'll likely respond to that. You have to remember that people here have their own jobs to do and are not offering free off-forum support for people who want to just target experts. One of the reasons I remain anonymous, I don't have time to be helping individuals on a personal level.
                        BluShadow
                        i will keep your points in mind :)
                        • 9. Re: Loading incremantal Data ::
                          983563
                          Hi Chanchal Wankhade,
                          Its advisable to use Merge or Clean updates statement Over Cursor For Loop.
                          1)Clean updates statement over cursor for loop i cant get this one .Can you explain it in brief . How can i clean
                          
                          2)What is the best way to do the incremental load (Either i have to use the Cursor for loop or i have to use the MERGE statement inside the procedure ) can you give me in brief .
                          
                          3)can  i use the collection like bulk collect for this 
                          
                          Please give me the suggestion for the above queries .I am awaiting for the answers .
                          Thanks & Regards,
                          Oracle developer.
                          • 10. Re: Loading incremantal Data ::
                            damorgan
                            Yes.
                            • 11. Re: Loading incremantal Data ::
                              Marwim
                              Hello,

                              could you please stop to put
                               tags around your text? It makes it harder to read because it prevents line breaks and we have to scroll to read your question.
                              
                              Regards
                              Marcus                                                                                                                                                                                                                                                                                                                                                                                                
                              • 12. Re: Loading incremantal Data ::
                                Marwim
                                2)What is the best way to do the incremental load (Either i have to use the Cursor for loop or i have to use the MERGE statement inside the procedure ) can you give me in brief .
                                You have been shown how to use MERGE and how to use a LOOP. Do you have a problem with the syntax? And you have been told that MERGE is usually a better choice then the LOOP. So do you have a question about a certain feature?

                                Regards
                                Marcus