Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2012
    Posts
    5

    Unanswered: How to ignore duplicate rows while backing up the Table

    Hi,

    I am new to Oracle DB, I have one requirement to insert (or backing up) the data from Table1 to Table2 where 4 columns having composit Key combination.

    Now I want to insert Changed data from Table1 to Table2.

    Ex: In table1 i have 3 rows with data as follows.

    Table-1

    col-1 Col-2 col-3 col-4........col-n
    12 ab cd af
    23 bc ab cd
    34 df xz cy

    In table 2


    col-1 Col-2 col-3 col-4........col-n
    12 ab cd af
    23 bc ab cd
    34 df xz cy

    after one week my table-1 will get updated with new data and then I want to back that up into table-2. In order to insert the new data from table-1 to table-2, i need to ignore the duplicate key insertion exception from table-2.

    Incase of any such exception I just need to populate the error message to the user and proceed with next record insertion.

    Note:- The above mentioned 4 columns are defined as composite keys.

    Resulatant table after back up should be as below.

    In Table-1

    col-1 Col-2 col-3 col-4........col-n
    12 ab cd af
    23 bc ab cd
    34 df xz cy
    21 xf at gh<-----This row is newly added in Table-1 hence i want this to go into table-2 as well.


    In table 2

    col-1 Col-2 col-3 col-4........col-n
    12 ab cd af
    23 bc ab cd
    34 df xz cy
    21 xf at gh



    Can you please suggest the solution for my above requirement?

    Thanks,
    Hari

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    You have two choices:

    • construct your INSERT statement such that you don't select existing rows in the first place
    • use the "LOG ERRORS INTO" option to automatically write rows with an error into a separate log table.
      For details see the manual: http://docs.oracle.com/cd/E11882_01/...4.htm#BGBEIACB

  3. #3
    Join Date
    Aug 2003
    Location
    West
    Posts
    101
    insert into table2
    select * from table1 where
    (col1, col2, col3, col4) not in
    (select col1, col2, col3, col4 from table2)

  4. #4
    Join Date
    Jun 2012
    Posts
    5
    Thank you Very much for response...it is working me & no need to use the log the errors into in my case..
    Once again Thank you..

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Also check out the MERGE command. It is fast and powerful.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •