Deleting Duplicate Records

Following steps used to delete duplicate records

1. Identify duplicates

select au_lname, au_fname, city, state, count(*)
from dup_authors
group by au_lname, au_fname, city, state
having count(*) > 1
order by count(*) desc, au_lname, au_fname

2. Temp Table and Truncate

select distinct *
into #holding
from dup_authors

3. Rename and Copy Back

sp_rename 'dup_authors', 'temp_dup_authors'

4. Create a Primary Key (prevention)

-- Add a new column
-- In real life I'd put an index on it
Alter table dup_authors add NewPK int NULL


Article from SQLTeam, more