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.
11-03-2004, 10:46 AM
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 ...
When occurance = 1 Then
Into MY_TABLE (col1, col2, ... coln)
When occurance > 1 Then
Into MY_ERROR (col1, col2, ... coln)
(Partition By col1,col2,col3,col4,col5,col6
Order By rownumber) occurance