Keywords: PSTL, Professional Services Tool Library, Duplicate Key
When using PSTL Combiner (i.e. combining Items, Customers or Vendors), you can get a duplicate key error:
[Microsoft][SQL Server Native Client 11.0][SQL Server]Violation of PRIMARY KEY constraint ‘PK-tablename-‘. Cannot insert duplicate key in object ‘dbo.-tablename-‘. The duplicate key value is (AAAAAAA, BBBBB)
This is usually caused by a table that has multi-part key, such as a pricing table where the key fields are:
- Item Number
- Vendor ID
- U of M
- Date
- Qty
In such a table if there is pricing for Item A and Item B, and you are combining B into A, there is no way for PSTL to “combine” two price lists. The same would be true for changing Vendor X to Vendor Y…it would create duplicate price lists and the software has no way to figure out which one to keep.
DO NOT FOLLOW THE STEPS BELOW IN A LIVE COMPANY DATABASE! MAKE SURE YOU HAVE TESTED AND VALIDATED BEFORE DOING THIS IN LIVE.
For the table getting the error, delete either the A or B set of records. You need to make a choice of which “source” will be the master record going forward, is it the records from A or from B? Then run the query below:
delete [tablename] where [ITEMNMBR/VENDORID/CUSTNMBR] = ‘A’
9MAY2023: Update: John Arnold at US Digital shared the script below (link is a text file) he created to identify problems with Customer Combiner prior to running the tool. Download Script