插入数据库表时如何定位主键重复行？ABAP技术问询

Great question! Let's break this down into your two core asks and walk through actionable solutions tailored to your ABAP scenario:

1. Locating Problematic Rows via System Variables or ST22 #

Let’s clarify what’s possible and where the limitations lie:

• ST22 Dump Analysis: When the short dump triggers, ST22 will show you critical details under the SQL Error or Database Exception section. You’ll see the exact duplicate primary key combination (MATNR, LIFNR, ZART) that caused the collision. However, it won’t directly point you to the specific row index in lt_table—it only reveals the conflicting key values.
• System Variables Limitation: For bulk INSERT FROM TABLE statements, SY-TABIX doesn’t reflect the exact failing row index. Bulk inserts are treated as a single database call; if any row fails, the entire batch rolls back, and SY-TABIX will typically hold the total number of rows in lt_table, not the problematic entry.
• Alternative Debugging Trick: If you need to pinpoint the exact rows in lt_table, temporarily replace the bulk insert with a row-by-row loop:

  LOOP AT lt_table INTO DATA(ls_table).
    TRY.
        INSERT zdbx FROM ls_table.
      CATCH cx_sys_open_sql_db.
        WRITE: / 'Failing row index:', sy-tabix, 'MATNR:', ls_table-matnr, 'LIFNR:', ls_table-lifnr, 'ZART:', ls_table-zart.
        EXIT.
    ENDTRY.
  ENDLOOP.
  

  This uses SY-TABIX to log the exact index of the duplicate row. Just remember to revert to bulk inserts after debugging for performance.

2. Catching Exceptions with TRY-CATCH & Logging Duplicates to Job Log #

Absolutely! You can use ABAP’s exception handling to catch the duplicate key error, identify conflicting entries, and log them to the job log (or foreground output). Even better, we can proactively detect duplicates before attempting the insert to avoid dumps entirely:

Example Implementation #

DATA(lt_duplicates) = VALUE zdbx( ).

* Step 1: Proactively find all duplicate primary keys in lt_table
SORT lt_table BY matnr lifnr zart.
DELETE ADJACENT DUPLICATES FROM lt_table COMPARING matnr lifnr zart
  TRANSPORTING NO FIELDS
  COLLECTING INTO lt_duplicates.

* Step 2: Log duplicates to job log if found
IF lt_duplicates IS NOT INITIAL.
  WRITE: / '⚠️ Found duplicate primary key entries. Details:', /.
  LOOP AT lt_duplicates INTO DATA(ls_dup).
    WRITE: / 'MATNR:', ls_dup-matnr, ' | LIFNR:', ls_dup-lifnr, ' | ZART:', ls_dup-zart.
    * Write to background job log (use this if running in batch)
    CALL FUNCTION 'BP_JOBLOG_WRITE'
      EXPORTING
        msgty = 'E'
        msgid = 'Z_CUSTOM_MSG'
        msgno = '001'
        msgv1 = ls_dup-matnr
        msgv2 = ls_dup-lifnr
        msgv3 = ls_dup-zart.
  ENDLOOP.
  * Optional: Export duplicates to Excel for user review
  CALL FUNCTION 'SAP_CONVERT_TO_XLS_FORMAT'
    EXPORTING
      i_filename = 'DUPLICATE_KEYS.xlsx'
    TABLES
      i_tab_sap_data = lt_duplicates.
  RETURN. * Halt processing until user cleans the data
ENDIF.

* Step 3: Safe insert if no duplicates
TRY.
    DELETE FROM zdbx.
    INSERT zdbx FROM TABLE lt_table.
    COMMIT WORK.
    WRITE: / '✅ Data inserted successfully.'.
  CATCH cx_sys_open_sql_db INTO DATA(lo_exc).
    * Fallback: Catch unexpected errors and log them
    WRITE: / '❌ Insert failed. Error:', lo_exc->get_text( ).
    CALL FUNCTION 'BP_JOBLOG_WRITE'
      EXPORTING
        msgty = 'E'
        msgid = 'SY'
        msgno = lo_exc->get_error_number( )
        msgv1 = lo_exc->get_error_message( ).
ENDTRY.

Key Notes: #

• Proactive Detection: Scanning for duplicates before the insert avoids short dumps entirely and lets you log all conflicting entries upfront—this is far more user-friendly than reacting to a dump.
• Job Log Integration: BP_JOBLOG_WRITE ensures duplicates are logged in the background job log for batch processing. For foreground runs, WRITE statements will display directly in the output screen.
• User Decision Support: Exporting lt_duplicates to Excel lets users easily review and choose which fact_code entries to keep, then reprocess the cleaned table.

内容的提问来源于stack exchange，提问作者Ovidiu Pocnet