deleting dups and keeping only 1 row in a batch

I have asked this question before and got some great answers, I just wanted to ask this again. I can detect dups easily, is there a way to get a total number of all dups so that I can delete a certain number at a time, then check the total again for verification that that specific number of dups are gone? I'm still using 2000. If I delete dups, won't it delete the 1 row I want to keep? And I do have a unique identity column.

thx,

Kat

Kat -

You can get the dup rows using Group by with Having clause - see example:

SELECT ident = convert(int,1)

,name = 'harry'

INTO #Temp

INSERT #Temp

(ident, name)

SELECT 2, 'tom'

UNION

SELECT 3, 'dick'

UNION

SELECT 4, 'harry'

SELECT ident = max(ident), name INTO #dupids from #temp group by name having (count(*) > 1)

you can then

DELETE #Temp WHERE ident in (SELECT ident from #Dupids)

count output from the first query should match the second

Thanks

AWAL

|||

Thank you, I haven't had time to test this out but I'm sure it works.

Kat