Quality control data import fails with duplicate records warning after running Data Mover scripts for inspection lots

[ericsys]

Our quality control data import jobs have started failing consistently with duplicate key violations. We’re importing inspection results from an external lab system into d365-10.0.41.

The error message:

Violation of PRIMARY KEY constraint 'PK_QualityTestResult'
Cannot insert duplicate key in object 'dbo.QualityTestResult'
The duplicate key value is (QC-2025-00142)

We’ve reviewed our import script and data cleaning procedures, but the source data doesn’t appear to have duplicates. The issue seems to be that some records already exist in the system, but our script isn’t detecting them properly. We need to handle unique constraints better during import.

Has anyone dealt with similar duplicate key issues in quality management imports?

[raymondbuilder]

This is a common issue with quality data imports. Your import script probably needs a MERGE statement instead of straight INSERT. Check if you’re using RecId or the business key (QualityTestResultId) for duplicate detection. The business key is more reliable for external imports.

[brendahacker]

The staging table approach sounds promising. Could you provide more details on how to structure the stored procedure? Specifically, how do we handle cases where a record exists but has been updated in the external system?

[rebecca_analyst]

For updated records, you need to compare timestamps or version numbers. Add a LastModifiedDateTime column to your staging table and compare it with the existing record’s ModifiedDateTime in QualityTestResult. If the staged record is newer, update the existing record. If older, skip it. Also add a ProcessingStatus column to track which staged records were inserted, updated, or skipped.

[jacobanalyst]

I’d recommend implementing a staging table approach. Import all records into a staging table first, then use a stored procedure to identify and handle duplicates before moving data to the final QualityTestResult table. This gives you better control over data cleaning and lets you log duplicate records for investigation rather than just failing the entire import batch. We use this pattern successfully for all our quality imports and it’s much more robust than inline checks.

[manoj_data]

We’re using INSERT statements with a preliminary check against QualityTestResultId. The strange thing is that the check query returns no results, but the INSERT still fails. Could there be a timing issue where records are being created between our check and insert operations? We’re importing about 500 records per batch.

[brandon_dev]

Yes, that’s definitely a race condition. If multiple import processes run simultaneously, they can all pass the existence check and then try to insert the same record. You need to wrap your check-and-insert logic in a transaction with appropriate isolation level. Use SERIALIZABLE or implement an UPSERT pattern with MERGE. Also check if your QualityTestResultId generation is deterministic - if the external system and D365 both generate the same IDs independently, you’ll get collisions.

[john_user]

Here’s a comprehensive solution addressing data cleaning, import script optimization, and proper unique constraint handling:

1. Data Cleaning Strategy: Before importing, implement a pre-validation step that identifies potential duplicates and data quality issues:

-- Pseudocode - Data cleaning validation:
1. Load external data into staging table QualityTestResultStaging
2. Run deduplication query on staging data using QualityTestResultId
3. Identify records with NULL or invalid foreign keys (ItemId, TestGroupId)
4. Flag records with conflicting timestamps for manual review
5. Generate data quality report before proceeding with import
-- See: Quality Management Data Import Guide

Create a staging table with audit columns:

CREATE TABLE QualityTestResultStaging (
    StagingId BIGINT IDENTITY PRIMARY KEY,
    QualityTestResultId NVARCHAR(50),
    TestDate DATETIME,
    TestResult NVARCHAR(20),
    ItemId NVARCHAR(50),
    ProcessingStatus NVARCHAR(20) DEFAULT 'Pending',
    ErrorMessage NVARCHAR(500),
    SourceModifiedDateTime DATETIME,
    ImportDateTime DATETIME DEFAULT GETDATE()
);

2. Import Script with Proper Duplicate Handling: Use a MERGE statement that handles all scenarios - insert new records, update existing ones, and log conflicts:

-- Pseudocode - Main import logic:
1. BEGIN TRANSACTION with SERIALIZABLE isolation level
2. MERGE QualityTestResult target using QualityTestResultStaging source
3. ON target.QualityTestResultId = source.QualityTestResultId
4. WHEN MATCHED and source newer: UPDATE target fields
5. WHEN NOT MATCHED: INSERT new record into target
6. Log all actions to QualityImportLog table
7. COMMIT TRANSACTION
-- Reference: SQL Server MERGE documentation

The actual MERGE implementation:

MERGE QualityTestResult AS target
USING QualityTestResultStaging AS source
ON target.QualityTestResultId = source.QualityTestResultId
WHEN MATCHED AND source.SourceModifiedDateTime > target.ModifiedDateTime
    THEN UPDATE SET
        TestResult = source.TestResult,
        TestDate = source.TestDate,
        ModifiedDateTime = GETDATE()
WHEN NOT MATCHED BY TARGET
    THEN INSERT (QualityTestResultId, TestResult, TestDate, ItemId, CreatedDateTime)
    VALUES (source.QualityTestResultId, source.TestResult, source.TestDate, source.ItemId, GETDATE());

3. Unique Constraint Management: Ensure your constraints are properly defined and add a unique index on the business key if it doesn’t exist:

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name = 'UX_QualityTestResult_BusinessKey')
CREATE UNIQUE NONCLUSTERED INDEX UX_QualityTestResult_BusinessKey
ON QualityTestResult(QualityTestResultId, ItemId);

Implement a constraint check procedure:

CREATE PROCEDURE sp_ValidateQualityImportConstraints
AS
BEGIN
    UPDATE QualityTestResultStaging
    SET ProcessingStatus = 'Invalid',
        ErrorMessage = 'Foreign key violation: ItemId not found'
    WHERE ItemId NOT IN (SELECT ItemId FROM InventTable);

    UPDATE QualityTestResultStaging
    SET ProcessingStatus = 'Duplicate',
        ErrorMessage = 'Duplicate within import batch'
    WHERE QualityTestResultId IN (
        SELECT QualityTestResultId
        FROM QualityTestResultStaging
        GROUP BY QualityTestResultId
        HAVING COUNT(*) > 1
    );
END;

4. Complete Import Workflow: a) Truncate staging table

b) Bulk insert external data into staging

c) Run sp_ValidateQualityImportConstraints

d) Execute MERGE for records with ProcessingStatus = ‘Pending’

e) Update staging records with results

f) Generate import summary report

5. Error Handling and Logging: Create a comprehensive logging table:

CREATE TABLE QualityImportLog (
    LogId BIGINT IDENTITY PRIMARY KEY,
    ImportDateTime DATETIME,
    RecordsProcessed INT,
    RecordsInserted INT,
    RecordsUpdated INT,
    RecordsSkipped INT,
    ErrorCount INT,
    ExecutionTimeSeconds INT
);

6. Best Practices:

• Run imports during low-usage periods to minimize lock contention
• Use batch sizes of 1000 records maximum for MERGE operations
• Implement retry logic with exponential backoff for deadlock scenarios
• Archive staging data for 30 days for troubleshooting
• Set up alerts for import failures or high error rates
• Document your QualityTestResultId format to prevent ID conflicts

This approach eliminates duplicate key errors by properly handling all scenarios before attempting database inserts. The staging table pattern gives you full visibility into what’s being imported and why certain records fail, making troubleshooting much easier.