MSSQL Tutorial: Delete All But 1 Duplicate Records
declare @id int, @total int
/* SELECT COUNT OF POTENTIAL DUPLICATES */
select id, count(id) from [tablename]
where [parameters] group by [column name]
/* LOOP TO CLEAN UP THE DUPLICATES */
declare aff_crsr cursor LOCAL static for
select id, count(id) from [tablename]
where [parameters] group by id
open loop_crsr
fetch from loop_crsr into @id, @total
while @@fetch_status = 0 begin
print cast(@id as varchar) + ‘ – ‘ + cast(@total as varchar) + ‘ – ‘ + cast((@total – 1) as varchar)
delete top (@total-1) from [tablename] where id=@id and [parameters]
fetch from loop_crsr into @id, @total
