-
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
)
/
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|