Finding duplicate values in a SQL table

Even though this is simple, I keep needing this every once in a while. This is for SQL Server.

To Find the list of fields for which there are duplicates (or multiple records), use:

SELECT [field1],[field2],[field3], count(*) as DUPE_COUNT INTO #DupeList FROM table_name GROUP BY [field1],[field2],[field3] HAVING count(*) > 1

Now to see the dupes:


Now, I prepare my delete … and verify I see only what I want to clean up:

SELECT * FROM table_name
WHERE [field1] in (SELECT [field1] FROM #DupeList)
ORDER BY [field1],[field2][field3]

Here we delete the dupes … be sure the query matches the select query to not delete unexpected data/values: (I commented this deliberated to avoid accidents)

–Clean up duplicate
–DELETE FROM table_name WHERE [field1] in (SELECT [field1] FROM #DupeList)

And we always clean up temp tables:

–Clean up the temp table

For more details: