How to remove duplicate records/data from Sql Server database table


Description: While working on asp.net web application with Sql server i need to remove the repeated records from the table so that i can fetch only unique records and display them. So after searching over internet i got the best trick to delete the duplicate records from the table although the table has the identity field.

Implementation: In Sql server create a Database e.g. Emp_DB and in that database create a  table using the script below.

CREATE TABLE [dbo].[Emp_Tb]
(
                [Emp_Id] [int] IDENTITY(1,1) NOT NULL,
                [EmpName] [varchar](100) NULL,
                [Age] [int] NULL,
                [Salary] [decimal](18, 2) NULL,
                [City] [varchar](100) NULL
) 
  • After creating the table insert some data having duplicate data as using the script below:

INSERT INTO Emp_Tb (EmpName,Age,Salary,City)VALUES ('Sunny',24,16500,'pkl')
INSERT INTO Emp_Tb (EmpName,Age,Salary,City)VALUES ('Pawan',23,12500,'Kalka')
INSERT INTO Emp_Tb (EmpName,Age,Salary,City)VALUES ('Varun',22,16000,'Chandigarh')
INSERT INTO Emp_Tb (EmpName,Age,Salary,City)VALUES ('Tarun',23,18000,'pkl')
INSERT INTO Emp_Tb (EmpName,Age,Salary,City)VALUES ('Pawan',23,12500,'Kalka')
INSERT INTO Emp_Tb (EmpName,Age,Salary,City)VALUES ('Tarun',23,18000,'pkl')

It will look like as:

remove duplicate records from sql server table


Note this table contains duplicate data and we want to remove that duplicate records from the table.
  • I am going to use CTE(Common table expression) to delete duplicate records as:
                WITH TempEmpDetails (EmpName,duplicateRecordCount)
                AS
                (
                                SELECT EmpName,ROW_NUMBER() OVER(PARTITION BY EmpName, Salary ORDER BY EmpName)
                                AS duplicateRecordCount  FROM Emp_Tb
                )
                DELETE FROM TempEmpDetails WHERE duplicateRecordCount > 1                
                SELECT * FROM Emp_Tb


It will delete all the duplicate records from the table as shown in figure below even though the Emp_Id is the identity field :

remove repeated records from sql server table

But suppose instead of removing all duplicate rows from table, we only want to remove the duplicate entry of a particular employee. For example we just want to remove the duplicate entry of Pawan, In that case the above query will be rewritten as:


WITH TempEmpDetails (EmpName,duplicateRecordCount)
                AS
                (
                                SELECT EmpName,ROW_NUMBER() OVER(PARTITION BY EmpName, Salary ORDER BY EmpName)
                                AS duplicateRecordCount  FROM Emp_Tb WHERE EmpName='Pawan'
                )
                DELETE FROM TempEmpDetails WHERE duplicateRecordCount > 1               
                SELECT * FROM Emp_Tb

Now over to you:
" I hope you have got the way to delete duplicate data from sql server table and If you like my work; you can appreciate by leaving your comments, hitting Facebook like button, following on Google+, Twitter, Linked in and Pinterest, stumbling my posts on stumble upon and subscribing for receiving free updates directly to your inbox . Stay tuned and stay connected for more technical updates."
Previous
Next Post »

11 comments

Click here for comments
Unknown
admin
November 07, 2013 ×

delete from Emp_Tb S
where S.rowid > any (select b.rowid from Emp_Tb b where B.EmpName=S.EmpName and B.Salary=S.Salary );

Reply
avatar
November 11, 2013 ×

thanks sir it is very useful for me :)

Reply
avatar
November 11, 2013 ×

Your welcome Azad Chouhan..keep reading :)

Reply
avatar
santanu
admin
January 25, 2014 ×

This is tremendous query ..:p

Reply
avatar
January 25, 2014 ×

Hi santanu..i am glad to hear that you find this article useful for you..stay connected and keep reading..:)

Reply
avatar
Anonymous
admin
March 08, 2014 ×

good approach , what if the state name is different ??

Reply
avatar
Unknown
admin
August 08, 2015 ×

Thank u sir

Reply
avatar
August 11, 2015 ×

Your welcome madhu reddy..stay connected and keep reading for more useful updates

Reply
avatar
Unknown
admin
December 02, 2015 ×

Hi Sir,

very useful code and scenarios you have given in webcodeexpert.com and It helps lot to me..
Thank you
Rahul

Reply
avatar
December 12, 2015 ×

Thanks for your valuable comment. Stay connected and keep reading for more useful updates..:)

Reply
avatar

If you have any question about any post, Feel free to ask.You can simply drop a comment below post or contact via Contact Us form. Your feedback and suggestions will be highly appreciated. Also try to leave comments from your account not from the anonymous account so that i can respond to you easily..