Violation of PRIMARY KEY constraint Msg 2627 Cannot insert duplicate key

Question

I am getting a primary key violation and I don't know how to resolve it. The actual error message is;

Msg 2627, Level 14, State 1, Line 1 Violation of PRIMARY KEY constraint 'PK_infmtx_dat_Transactions'. Cannot insert duplicate key in object 'dbo.infmtx_dat_Transactions'.

My code is as follows:

INSERT INTO infmtx_dat_Transactions (tranid,chgid,chgidagnst,incnumagnst,rptpd,aid,claimid
,chgsvcpd,trantype,doschg,doscalpd,postdtchg,postdtchgcalpd,postdttran
,postdttrancalpd,depositdt,depositcalpd,cptid,cptcode,cptcomp,billprov
,rendprov,facid,posid,dptid,priminsmne,priminscatid,transcode,crcat
,refprovid,modalid,units,adjunits,patcnt,enccnt,cptcnt,amt,chgallow
,totworkrvu,totfacrvu,denial,curresponsible,curbal,curinsmne
,curopenbalflag,curcreditbalflag,denyflag,denycode,denydate,feetypeid )
SELECT
trn.tran_id
,trn.chg_id
,chg.chgidagnst
,chg.incnumagnst
,trn.rptpd
,trn.acctid
,chg.claimid
,chg.rptpd
,tcd.trantype
,chg.doschg
,chg.doscalpd
,chg.postdtchg
,chg.postdtchgcalpd
,trn.tranpostdt
,trn.tranpostpd
,trn.pmtdate
,trn.pmtpd
,chg.cptid
,chg.cptcode
,chg.cptcomp
,chg.billprov
,chg.rendprov
,chg.facid
,chg.posid
,chg.dptid
,chg.priminsmne
,chg.priminscatid
,trn.payermne
,tcd.crcat
,chg.refprovid
,chg.modalid
,0
,0
,0
,0
,0
,trn.trnamt
,chg.chgallow
,0
,0
,0
,''
,0
,''
,'N'
,'N'
,'N'
,''
,Null
,chg.feetypeid
FROM tmp_dat_OtherTrans trn
LEFT JOIN infmtx_dat_Transactions chg on trn.chg_id = chg.tranid AND trn.chg_id = chg.chgid
AND trn.chg_id = chg.chgidagnst 
LEFT JOIN infmtx_dic_TransCode tcd on trn.payermne = tcd.trancodemne
ORDER BY trn.tran_id;

How would I set a query to find the duplicate records.
The primary keys on the infmtx_dat_Transactions table are:tranid,chgid,chgidagnst,rptpd and trantype

Answer 1

The primary key (and other candidate keys) prevent inserting duplicate values, so you can't search for them. Duplicates don't exist.

Instead, find the primary key (and other candidate key) values in the new data you're trying to insert, and search for them. One of them surely duplicated in your new data, or is already in your table.

It looks like you could identify conflicting keys in your existing table by executing a query with an inner join between your source table "tmp_dat_OtherTrans" (including its joins) and "infmtx_dat_Transactions".

Answer 2

WITH duplicate_check AS (
  SELECT 
    *,ROW_NUMBER() OVER(PARTITION BY key_column1,key_column2,key_column3 ORDER BY (SELECT NULL))) AS n
  FROM table_with_suspect_data
SELECT
  *
FROM duplicate_check
WHERE n > 1

For deleting all rows except one for each duplicate:

 WITH duplicate_check AS (
  SELECT 
    *,ROW_NUMBER() OVER(PARTITION BY key_column1,key_column2,key_column3 ORDER BY (SELECT NULL))) AS n
  FROM table_with_suspect_data
DELETE
FROM duplicate_check
WHERE n > 1