When i am using below merge into statement where i am trying to insert 200 resords and encounter with Referential integrity violation for 80 records and logged them into to error table with actual data.
i am wondering why the merge statement wouldn't continue and insert the error free of 180 records into my target table(DW_LOAD).it just simply roll back.
and also noticed if i have any data type issues while inserting my merge statement is allowing error free records into Target table(DW_LOAD) and logging error records into error table but this time it inserting only null values into all data columns except "ETC_" columns.
But i am expecting to insert all error free records in RI violation case can you please advise how to achieve that.
CREATE ERROR TABLE DW_LOAD_ERROR FOR DW_LOAD;
MERGE INTO DW_LOAD
USING STG_FLOAD FL
ON FL.STG_PK = DW_LOAD.DW_PK
WHEN NOT MATCHED THEN INSERT
LOGGING ALL ERRORS WITH NO LIMIT;
Referential Integrity errors are recorded in the Error Table, but always cause a rollback:
When the system encounters USI or RI errors (or both) in the MERGE operation, the following events occur in sequence:a The transaction or request runs to completion
b The system writes all erring rows into the error table
c The system aborts the transaction or request
d The system rolls back the transaction or request
Note that the system does not invalidate indexes, nor does it roll error table rows back, enabling you to determine which rows in the MERGE set are problematic and to determine how to correct them.
If the number of errors in the request is large, running it to completion plus rolling back all the INSERT and UPDATE operations can exert an impact on performance. To minimize the potential significance of this problem, you should always consider specifying a WITH LIMIT OF error_limit clause.
Thank you Dieter that makes sense.
Also can youplease tell me how to identify perticular row of original table using ETC_RowId column from Error table.