Duplicate row removal from a table

The way to do this is to create a copy of the table using

In this example I will assume I have a table called apple in database plum, and the column you want to make unique is called fruit

select * into tempdb..apple

from plum..apple

next create an identity column on this new table:

alter table tempdb..apple

add pip numeric(5,0) identity

The following query will now eliminate the duplicate entries of fruit;

delete from tempdb..apple

where pip in (select a.pip from tempdb..apple a, tempdb..apple b

where a.fruit = b.fruit

and a.pip > b.pip)

You will now have the table unique on the column fruit and you can put the data back into the original table (minus the identity column created with the following:

First delete or truncate the original plum..apple


Select fruit,… , <columns in plum..apple>

Into plum..apple

From tempdb..apple