How Do I Find Duplicate Rows?

Q: I’m trying to add a primary key to a table that I imported into SQL Server, but I can’t because SQL Server Management Studio (SSMS) is telling me that there are duplicate key values. I don’t want to have duplicate keys. How can I find out which rows in the table contain duplicate values?

A: The easiest way to find the duplicate values is by running a T-SQL query that groups like values together, aggregates their count, and then selects only those values with a count that’s greater than one. The following T-SQL statement shows how to use this method to find duplicate values in the table named Articles where the potential key columns are columns named ArticleID and Status:
<div> </div> <div>SELECT ArticleID, Status, count(*)</div> <div>FROM Articles</div> <div>GROUP BY ArticleID, Status</div> <div>HAVING count(*) > 1</div> <p> </p>

Discuss this Blog Entry 1

on Mar 10, 2010
OK, Let's take this one step farther.....

How do I leave one of the dup records [ArticleID, Status] in the original table and create a table of the remaining dup records?

Please or Register to post comments.

What's Database Administration Blog?
Blog Archive

Sponsored Introduction Continue on to (or wait seconds) ×