Finding a duplicate key when importing data

Posted Wednesday, January 18, 2006 10:07 AM by C-Dog's .NET Tip of the Day

This isn't really .NET 2.0 related or anything, but I still think it might be useful. I don't know how many times I have been charged with a task of importing data from a text file or from an access database or whatever and I ran into an issue where the supposed primary keys have duplicate rows in them. Well, with the aid of google, I found a relatively easy way to find the duplicates using the HAVING SQL clause.

The query below, will spit out the values committing the offense against the primary key. You may need to import your data to a temp table in SQL first without a key set. It will also work directly in Access.

SELECT COUNT(*), PrimaryKeyColumn
FROM TableName
GROUP BY PrimaryKeyColumn
HAVING Count(*) > 1

That's all there is to it. If you have a compound primary key, you can find it in a similar way.

SELECT COUNT(*), PrimaryKeyColumn1, PriamryKeyColumn2
FROM TableName
GROUP BY PrimaryKeyColumn1, PrimaryKeyColumn2
HAVING Count(*) > 1

That's not .NET related, but I hope it helps you out someday.

Read the complete post at