Technical Article

Delete Duplicate Records without using Cursor

,

This script will delete the duplicate records without using cursor

-- create table temp1 with duplicate records 

select * into temp1 from ( 
select 1 EMPNo , 'Anthony Koska' EMPName 
union all 
select 2 EMPNo , 'Muthu' EMPName 
union all 
select 1 EMPNo , 'Anthony Koska' EMPName 
union all 
select 3 EMPNo , 'Vivek' EMPName 
union all 
select 1 EMPNo , 'Anthony Koska' EMPName 
union all 
select 4 EMPNo , 'Srinivas' EMPName 
union all 
select 2 EMPNo , 'Muthu' EMPName 
union all 
select 4 EMPNo , 'Srinivas' EMPName 
) a 

-- Create a temp table which stores only the duplicate records empno 
select EmpNo into #temp  from temp1 group by EmpNo having Count(EmpNo) > 1 
declare @loopCnt as int 
declare @recCnt as int,@empno as int 
select @loopCnt = Count(1) from #temp 
---Total count of the duplicate records 

while(@loopCnt>0) 
begin 
--select the first records from #temp 
        set @empno = (select top 1 EmpNo from #temp) 
--set rocount is used since delete statement will not work with top 
        select @recCnt = Count(1)-1 from temp1 where EmpNo = @empno 
        set rowcount @recCnt 
--delete the duplicate records 
        delete from temp1 where EmpNo = @empno 
        set rowcount 0 
--delete the first record of #temp to fetch next record 
        delete from #temp where EmpNo = @empno 
--decremnt the loopcount 
        set @loopCnt = @loopCnt -1 
        
end 
select * from temp1 order by empno 
drop table temp1 
drop table #temp

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating