Archive for: ‘February 2020’

“Cannot insert duplicate key row” -error in Transform.Common

February 5, 2020 Posted by Alexander Axberg

I recently ran into the following error in the Transform-job in Data Warehouse:

An error countered while attempting to execute ETL Module:
 ETL process type: Transform
 Batch ID: 5601
 Module name: TransformIncidentDim
 Message: ErrorNumber=”2601″ Message=”Cannot insert duplicate key row in object ‘dbo.IncidentDim’ with unique index ‘UniqueIndex’.” Severity=”14″ State=”1″ ProcedureName=”TransformIncidentDimProc” LineNumber=”163″ Task=”Inserting into Dimension”

The error occures when the transform job was trying to instart a specific Incident into the dbo.IncidentDim table in the DWRepository database.
The source of the error was that the specific category in this incident, existed 2 times in the IncidentClassification table, but I guess the same thing could happen to the support groups or any other enum in the incident class.


  1. Open the IncidentClassification table in the DWRepository database, and look for enums with duplicate rows (but with different classificationID) with this query:
    SELECT count(enumTypeId) as ‘Count’ ,enumTypeId FROM IncidentClassification GROUP BY enumTypeId
  2. Look for a row where the count number is greater than 1. When found, copy the enumTypeId value and run this query:
    SELECT * FROM IncidentClassification WHERE enumTypeID = ‘<enumtypeID value>’
  3. To decide which duplicate row to remove, run a query against DWDataMart to see which one of the rows are used.
    Run this query against DWDataMart for each IncidentClassificationID above:
    SELECT * FROM IncidentDimvw WHERE Incidentclassification_IncidentClassificationID = ‘<IncidentclassificationID value>’
  4. For the row/rows in #2 that are not used in DWDataMart, delete them from DWRepository by using each the of IncidentClassificationIDs so only one row remains:
    DELETE FROM IncidentClassification WHERE IncidentClassificationID = ‘<IncidentclassificationID value>’
  5. Rerun the Transform.Common job, and it should complete successfully.