Why would this statement produce Primary Key violation error?

Question

Why would this statement produce Primary Key violation error?

Naomi Nosonovsky 8,881

Hi everyone,

Why would this code produce primary key violation error?

DROP TABLE IF EXISTS #temp
SELECT TOP (1000) 

indiv.npi, TRIM(lastname +', '+ firstname + ' ' + LEFT(middlename, 1)) AS fullname, firstname, lastname, 
LEFT(middlename, 1) AS middle_initial,
CURRENT_TIMESTAMP AS create_dtm, CURRENT_TIMESTAMP AS last_update_dtm,  
CURRENT_USER AS last_update_user
INTO #temp
FROM consume_tbl.PROV_PMF_INDIV_PROVIDER indiv
LEFT JOIN dbo.RPT_VA_Mission_Act_Provider miss
ON indiv.npi = miss.npi
WHERE miss.npi IS NULL AND indiv.NPI IS NOT NULL
AND indiv.npi <> '1003001272'
ORDER BY indiv.npi


INSERT INTO dbo.RPT_VA_Mission_Act_Provider
(
    npi,
    provider_fullname,
    provider_first_name,
    provider_last_name,
    provider_middle_initial,
    create_dttm,
    last_update_dttm,
    last_update_user
)
SELECT npi, cte.fullname, cte.firstname, cte.lastname, cte.middle_initial,
cte.create_dtm, cte.last_update_dtm, cte.last_update_user
FROM #temp cte WHERE NOT EXISTS(SELECT 1 FROM dbo.RPT_VA_Mission_Act_Provider 
WHERE cte.npi = RPT_VA_Mission_Act_Provider.npi)

Answer accepted by question author

0 additional answers

Your answer

Answer 1

The primary key violation error in your SQL statement can occur for several reasons:

Duplicate Values: The INSERT statement attempts to insert rows into the dbo.RPT_VA_Mission_Act_Provider table where the npi column is defined as a primary key. If the npi values being inserted already exist in the dbo.RPT_VA_Mission_Act_Provider table, this will trigger a primary key violation error.
NOT EXISTS Clause: While you have a WHERE NOT EXISTS clause intended to prevent duplicates, it may not be functioning as expected. If there are concurrent transactions inserting into the same table, or if the #temp table contains duplicate npi values, the check may not prevent all duplicates from being inserted.
Data Integrity: Ensure that the data in consume_tbl.PROV_PMF_INDIV_PROVIDER does not contain duplicates for the npi column, especially after applying the filters in your SELECT statement.
Temporary Table: If the #temp table is populated with duplicate npi values before the INSERT operation, it will still attempt to insert those duplicates into the target table, leading to a violation.

To resolve this issue, you can:

Check for duplicates in the #temp table before the INSERT operation.
Ensure that the npi values in #temp are unique.
Consider using a DISTINCT clause in your SELECT statement to eliminate duplicates from the temporary table.

By addressing these points, you should be able to prevent the primary key violation error from occurring.

Naomi Nosonovsky 8,881 Reputation points

2025-11-09T21:12:02.29+00:00

Adding DISTINCT seems to resolve the issue

Share via

Why would this statement produce Primary Key violation error?

0 additional answers

Your answer