Tuesday, 10 June 2014

Delete duplicate records using Cursor and ROW Count

Delete duplicate records except one record.


Declare @ID int
Declare  @Name varchar(100)

set @ID=0
Set @name=''

DECLARE Employee_Cursor CURSOR FOR

select Count(*) as ID,name from SRC group by Name

OPEN Employee_Cursor;

FETCH NEXT FROM Employee_Cursor INTO
 @ID,@Name;
WHILE @@FETCH_STATUS = 0
   BEGIN
                        Declare @Count int
                        set @Count=       @ID-1

                        set ROWCOUNT @Count

                        if(@Count>1)
                        begin
                                    delete from SRC where name like @Name
                        end
                        --print @name
                        set ROWCOUNT 0
                                     
      FETCH NEXT FROM Employee_Cursor INTO
 @ID,@Name;
   END;
CLOSE Employee_Cursor;
DEALLOCATE Employee_Cursor ;
GO

No comments:

Post a comment