How to remove duplicate values inserted into an identity column in a table?

Question

By some setting as using IDENTITY_INSERT we have duplicate entry in identity column, What is the best method of removing duplicate entry.

I have a table Details With column DetailID | FKey | Col1 | Col2 | Col3 | Col4

DetailID is "Identity" and FKey is foreign key with another table.

Now we have already 240000 record. Some one used "IDENTITY_INSERT" which was not for this table but by mistake it applied to it. So it hase record with duplicate identity.

So first we need to pick only those row which have same identity after that we need to match data of all column if all are same then keep only one row and remove others but is only identity is duplicate then we need to update identity value.

Answer 1

Hi,

http://www.kodyaz.com/articles/delete-duplicate-records-rows-in-a-table.aspx

Answer 2

You could use a CTE (Common Table Expression) in SQL Server 2005 and up to achieve this - you basically "partition" your data by the ID, so each group of identical values is a partition, and then you number those sequentially using ROW_NUMBER(). Any values of ROW_NUMBER() larger than 1 indicate a duplicate.

;WITH Duplicates AS
( 
    SELECT 
      DetailID, FKey, Col1, Col2, Col3, Col4,
      ROW_NUMBER() OVER (PARTITION BY DetailID ORDER BY FKey) AS 'RowNum'
    FROM dbo.YourTable
)
SELECT 
  DetailID, FKey, Col1, Col2, Col3, Col4, RowNum
FROM Duplicates
WHERE RowNum > 1

This will produce a list of duplicates for you - you can now either update those, or delete them, or whatever you want to do with them.