Sunday, October 5, 2008

Deleting Duplicate batches of rows

This code works in SQL Server 2005 and above, because it uses the delete top (N) statement. Although using a cursor is not always a good idea but there are situations where we have to use it.

if object_id('tempdb.dbo.##Employee') IS NOT NULL
DROP TABLE dbo.##Employee

if object_id('tempdb.dbo.##Emp1') IS NOT NULL
DROP TABLE dbo.##Emp1

create table dbo.##Employee
(
EmpName varchar(30)
)

insert into ##Employee(EmpName) values('Abc')
insert into ##Employee(EmpName) values('Abc')
insert into ##Employee(EmpName) values('George')
insert into ##Employee(EmpName) values('Micky')
insert into ##Employee(EmpName) values('Micky')
insert into ##Employee(EmpName) values('Micky')


select empname,(count(1)-1) cnt
into ##Emp1
from ##Employee
group by empname
having count(1) > 1

DECLARE @empname varchar(30), @cnt int
DECLARE cur CURSOR FOR select empname,cnt from ##Emp1
OPEN cur

FETCH NEXT FROM cur INTO @empname, @cnt
WHILE @@FETCH_STATUS = 0
BEGIN

delete top (@cnt) from ##Employee where empname = @empname

FETCH NEXT FROM cur INTO @empname, @cnt
END
CLOSE cur
DEALLOCATE cur

select * from ##Employee

No comments: