SQL Server Performance Forum – Threads Archive

T-SQL help

I have a duplicate primary key violation when inserting data. Using T-SQL how would I find the duplicate the quickest?

---

SELECT field1, field2,.., COUNT (*)
FROM Table (NOLOCK)
GROUP BY field1, field2,….,
HAVING COUNT(*) > 1 Luis Martin
Moderator
SQL-Server-Performance.com Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason.
Leonardo Da Vinci Nunca esperes el reconocimiento de tus hijos, eso ocurrirá luego de tu muerte
All postings are provided “AS IS” with no warranties for accuracy.

---

http://www.sqlteam.com/item.asp?ItemID=15149 Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

Hi Luis, The sproc in inserting data into an archive table. Here’s what I wrote: select a.*, s.*
from tblSecurities_Archive a inner join tblSecurities s
on a.info_Date = s.info_Date
and a.info_Type = s.info_Type
and a.Security_ID = s.Security_ID
and a.Info_Value = s.Info_Value It returns 48 rows. The primary key is info_date, info_Type and Security_ID Both tables have the exact same cols. Shouldn’t the above statement work? By the way, the error returned when the stored procedure tries to insert data from one table into the archive table is: Violation of PRIMARY KEY constraint ‘PK_tblSecurities_Archived’. Cannot insert duplicate key in object ‘tblSecurities_Archive’.
The statement has been terminated. An extra pair of keys is appreciated. Thanks.

---

there are two types of duplicates when inserting,
1. duplicate in the destination table
– you can use the NOT EXISTS constraint or LEFT JOIN/WHEN NULL to avoid duplicate in the destination table.
2. duplicate from the source table
– you can use the MAX/MIN of the UNIQUE ID in conjuction with the HAVING COUNT query above to select the first/last of the record to insert. i.e.
SELECT *
FROM TABLE
WHERE ID IN (
SELECT MAX(ID)
FROM table
GROUP BY FIELD1, FIELD2, ..
HAVING COUNT(*)>1
) May the Almighty God bless us all!
www.empoweredinformation.com

---

]]>