How to remove duplicate values ​​inserted in an identifier column in a table?

Question

In some settings using IDENTITY_INSERT, we have a duplicate entry in the Identifier column. What is the best way to delete a duplicate entry.

I have a Details table with a DetailID column | Fkey | Col1 | Col2 | Col3 | Col4

DetailID is "Identity", and FKey is a foreign key with a different table.

We now have 240,000 records. Someone used "IDENTITY_INSERT", which was not for this table, but mistakenly applied it to it. Thus, it writes a record with a duplicate identifier.

So, first we need to select only those rows that have the same identifier, after which we need to match the data of the entire column, if all are the same, then save only one row and delete the others, but only the identity is duplicated, then we need to update the identifier value.

Answer 1

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

Answer 2

CTE (Common Table Expression) SQL Server 2005 - "" ID, , ROW_NUMBER(). ROW_NUMBER() 1 .

;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

- , , , .