Is there any T-SQL to search for duplicates ? (*** SOLVED ***)

[Wen Lie]

Hi...

Let's say like this... I have an old database in .DBF format. I transfer the database to SQL Server using DTS. Result of DTS is the table(s) from .DBF with no Primary Key.

When I need to set up the PK (primary key), SQL returns error, and said something likes "Duplicate key found for record .... and ..."

My question is :
before I setup the PK, can I find which record has its duplicate(s) by using T-SQL at Query Analyzer ???

FYI : I'm using SQL Server 2K

[szlamany]

Lets say the table your are checking is called THETABLE and the column you are interested in seeing if it has dups is called THECOLUMN

SELECT THECOLUMN,SUM(1) FROM THETABLE GROUP BY THECOLUMN HAVING SUM(1)>1

[Wen Lie]

hmm...
huh....

that simple ? =p~
wew... i try to create my own T-SQL, so complicated, nd not workin' at all ;p

hahaha.... lolz
thanks a lot szlamany for the T-SQL... it works.