Wednesday, September 27, 2006

Simple recipe for eliminating duplicates

I've used this code over and over, so I better save it for future ocassions.
Problem: A table (MyTable) with a field (name) I need to eliminate records with duplicated name values.

declare @name_unique varchar(50)
declare @tablename table (name_final varchar(50), value text) ---every other field in MyTable

DECLARE cursor_uniquename CURSOR
FOR select distinct name from MyTable

OPEN cursor_uniquename

FETCH NEXT FROM cursor_uniquename
INTO @name_unique

WHILE @@FETCH_STATUS = 0
BEGIN

insert into @tablename select top 1 name,value from MyTable where name=@name_unique

FETCH NEXT FROM cursor_uniquename
INTO @name_unique
END

CLOSE cursor_uniquename
DEALLOCATE cursor_uniquename

truncate MyTable

insert into MyTable select * from @tablename


I wonder if there is a more efficient way, hrmmm...

0 Comments:

Post a Comment

<< Home