DeDuping Tables with Duplicate Records

-- Script by Yomi Obe, Consultant SQL DBA
-- yomi@esus.co.uk
-- July 2007
-- Version 1.00

SELECT     COUNT(*) AS DuplicateCount, [Column1], [Column2], [Column3]
FROM         dbo.DuplicateTable
GROUP BY [Column1], [Column2], [Column3]
HAVING      (COUNT(*) >= 2)

Select T1.* from dbo.DuplicateTable as T1 join dbo.DuplicateTable as T2
on ltrim(rtrim(T1.[Column1])) = ltrim(rtrim(T2.[Column1])) and
ltrim(rtrim(T1.[Column2])) = ltrim(rtrim(T2.[Column2])) and
ltrim(rtrim(T1.[Column3])) = ltrim(rtrim(T2.[Column3]))
where T1.[TableID] <> T2.[TableID]

if 

Select ltrim(rtrim([Column1])) as [Column1], ltrim(rtrim([Column2])) as [Column2], 
ltrim(rtrim([Column3])) as [Column3], 
DupCount=count(*), IDKey = max([TableID])
into #Duplicates
from dbo.DuplicateTable
group by 
[Column1], [Column2], [Column3]
having count(*) >= 2
order by count(*) DESC 

Deletedbo.DuplicateTable
fromdbo.DuplicateTable DT join #Duplicates D
on
DT.[Column1] = D.[Column1] and
DT.[Column2] = D.[Column2] and
DT.[Column3] = D.[Column3]
whereDT.[TableID] not in (select IDKey from #Duplicates)

SELECT     COUNT(*) AS DuplicateCount, [Column1], [Column2], [Column3]
FROM         DuplicateTable
GROUP BY [Column1], [Column2], [Column3]
HAVING      (COUNT(*) >= 2)

drop table #Duplicates

PRINT 'If no records are returned on the last column, the de-duping is successful!'