SQL Statement for finding duplicates

29 Jun 2006 8:23 PM

rwyarger

Hi -

Sorry for this post (as I'm sure it's been answered a million times by
now), but I wasn't able to find a real clear example of what I am
trying to do.

I have a customer who has some data that I am wanting to import in
through a DTS package. Problem is that there are over 500 records in
this file (so going through it manually is out). I've tried the import
and it continues failing. My guess is that there is some duplicate
data in there that is causing it to hang. I need to make my col001 my
key field, and the col002 just an ordinary non-nullable field (actually
both fields are non-nullable).

The problem I have is I cannot remember the SQL statement for finding
duplicates. If col001 is a column named "Account" and col002 is a
column named "Departments" I want to be able to find out if any given
account is being used by more than 1 department. Departments may have
between 1 to 10 different accounts, but no single account should be
used by more than 1 Department.

Hope this isn't too confusing, but just a simply generic SQL statement
from anyone that will help me find the account number dups would be
great.

Thanks

29 Jun 2006 8:32 PM

Aaron Bertrand [SQL Server MVP]

Insert it into a working table that does not have the constraints. Then,
run one of the many examples of de-dupe queries out there (e.g. see
http://www.aspfaq.com/2431) before moving the data from the working table to
the real table.

A