Msg 2627 level 14 line 1 PRIMARY KEY constraint

[vba317]

I am getting this error when I run this query. The full error 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'.
The statement has been terminated.
When I looked at infmtx_dat_Transactions it has 5 primary keys:tranid,chgid,chgidagnst,rptpd,trantype.
Is there a way to write a select statement to find out what the duplicates are? Any help is apprecaited. Tom

/*** POST TRANSACTIONS LIVE ***/
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;

[code]

 

[gmmastros]

Usually in a situation like this, you need to write 2 queries.

The first query should insert data in to the destination table where the data does not already exist.

The second query should update the data in the destination table for those records that match the destination table.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[vba317]

How would I do this?

 

[vba317]

I have decided to create an excel spreadsheet and do vlookups to find the duplicate records. Thanks for your assistance.

Tom

 

[gmmastros]

I apologize that I haven't got the time to show you the query.

However, this is not as simple as it seems. Since you have a multi-column primary key, there will be no duplicates in the infmtx_dat_Transactions table. There can be 2 situations that cause the error you are getting.

1. There could be duplicates in the source table(s).
2. There could be a row in the destination table, and a row in the source tables that have the same duplicate key values (combination of the columns). Basically, there wouldn't be a duplicate in the destination table, but by adding data to it, there would be a duplicate. Since duplicates are not allowed, you get the error.

Again, I apologize for not having the time to help more. Hopefully someone else can.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[vba317]

No problem,
I have found that the duplicates are in the tmp_dat_OtherTrans table. So I decided to write a delete query. Until I verify the delete query is working correctly I have it keyed on 1 record which has four duplicates. The problem I am now having is that when I run the delete query instead of deleting 3 records it leaves three records and deletes 197,000 records.

DELETE
FROM tmp_dat_OtherTrans 
WHERE tran_id NOT IN
(SELECT MIN(tran_id)
FROM tmp_dat_OtherTrans 
WHERE tran_id = 1187461
GROUP BY tran_id, chg_id, rptpd)