Delete duplicate Row in sql server

Delete Duplicate Row in sql server

create table test(name varchar(20),rollNo int)

insert into test values('santosh',1)
insert into test values('santosh',1)
insert into test values('santosh',1)
insert into test values('santosh',1)
insert into test values('santosh',1)
insert into test values('santosh',1)
insert into test values('alok',2)
insert into test values('alok',2)
insert into test values('alok',2)
insert into test values('alok',2)
insert into test values('alok',2)
insert into test values('alok',2)

SET ROWCOUNT 0 --execute rowcount 0 after executing the cursor
select * from test order by name


--Create cursor for duplicate row delete

Declare @Count int
DECLARE @rollNo int
DECLARE @name nvarchar(20)


DECLARE dublicate_cursor CURSOR FAST_FORWARD FOR
SELECT name, rollNo, Count(*) - 1
FROM test
GROUP BY name, rollNo
HAVING Count(*) > 1

OPEN dublicate_cursor

FETCH NEXT FROM dublicate_cursor INTO @name,@rollNo,@Count

WHILE @@FETCH_STATUS = 0
BEGIN
SET ROWCOUNT @count
--DELETE TOP(@Count) FROM test WHERE name = @name AND rollNo = @rollNo
DELETE FROM test WHERE name = @name AND rollNo = @rollNo

FETCH NEXT FROM dublicate_cursor INTO @name, @rollNo, @Count
END

CLOSE dublicate_cursor
DEALLOCATE dublicate_cursor


Note:- SET ROWCOUNT @count is used when you are using sql server 2000
In sql server 2005 you can use DELETE TOP(@Count) FROM test WHERE name = @name AND rollNo = @rollNo


Thanks and enjoy