Deleting Duplicate Records from table with text, ntext, or image data type
————–Deleting Duplicate Records from table with text, ntext, or image data type ———————-
?
–Step 1
—Create a Primary Key
select * from catdetails
–Step 2
—Create a Primary Key
Alter table catdetails add NewPK int NULL
–Step 3
—populate the new Primary Key
declare @intCounter int
set @intCounter = 0
update catdetails
SET @intCounter = NewPK = @intCounter + 1
–Step 4
–Get one primary key value and insert into the tblTemp1 table
select subcatid, title, RecCount=count(*), PktoKeep = max(NewPK)
into tbltemp1
from catdetails
group by subcatid, title
having count(*) > 1
order by count(*)
–Step 5
–delete duplicated except one Primary key for each duplicated record
delete? catdetails
from? catdetails a
where? a.NewPK not in (select PKtoKeep from tbltemp1)
–Step 6
—remove the NewPK column
ALTER TABLE catdetails DROP COLUMN NewPK
?
–Step 7
drop table tbltemp1
