Monday, December 19, 2011

Delete Duplicate Records Or Rows - Sql Server

Delete/Remove duplicate records from MS Sql Server      
  we have 3 methods to do this
like we have table emp with column fname,dept
 table:  emp
fname           dept 
aditya           mca
abhishek       mca
akhilesh        mca
anand           mba
ram niwas     mba
aditya          mca
abhishek      mca
arbind          btech

(aditya mca) & (abhishek mca)  rows are deuplicates in above table so we have three methods to remove duplicacy

Method 1:- 
by using Identity Column

step1.   create a identity column in our table

alter table emp add id int identity(1,1) 

step 2 write this query 

delete from emp 
where id not in  (select min(id)
from  emp  group by fname,dept)

Method 2:-

Delete duplicate records using Row_Number()

 If you do not want to make any changes in table design or don't want to create identity column on table then you can remove duplicate records using Row_Number in sql server 2005 onwards.

WITH DuplicateRecords AS
SELECT *,row_number() OVER(PARTITION BY fname,dept ORDER BY fname 
AS RowNumber FROMemp
DELETE FROM DuplicateRecords WHERE RowNumber>1
Method 3:-

Remove duplicate rows/Records using temporary table

by using distinct

SELECT distinct * into TempTable FROM emp
GROUP BY fname,dept
HAVING COUNT(fname) > 1
DELETE emp WHERE fname
IN (SELECT fname FROM TempTable)




No comments:

Post a Comment