dup val on index

Hi Gurus,

I need once again your help.

I'm inserting records from an external table, and it's possible that this recordset is already in the internal table - then the dup_val_on_index exception is raised. That works, but I have to insert this recordset into an error table containg the PK from the original entry. e.g.

inserting a record which is in table a with primary key a_id - and when exactly the same recordset (unique index 6 columns) is tried to be inserted again - the record is inserted into the error table and the foreign key should be the primary key (a_id) of the 'unique' recordset.


thanks for your help.

You want the first occurance to go in the regular table, and subsequent ones to go into an error table?

Try something like a multitable insert combined with an analytic function ...

Code:

---

Insert
  When occurance = 1 Then
  Into MY_TABLE (col1, col2, ... coln)
  When occurance > 1 Then
  Into MY_ERROR (col1, col2, ... coln)
Select
  col1,
  col2,
  ...
  coln
From
  (
  Select
      col1,
      col2,
      ...
      coln,
      Row_Number() Over
        (Partition By col1,col2,col3,col4,col5,col6
          Order By rownumber) occurance
  From
      source_table
  )
/

---