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

Introduction: In this article I am going to explain How to delete/remove the duplicate/redundant/repeated records from the Sql server table.
In previous articles i explained How to Create Sql server database script and Create database from that script and Delete all stored procedures from sql server database and Convert SQL Server 2012,2008 database to SQL Server 2005 or lower version and Delete multiple records from asp.net gridview with checkbox selection and Bind and implement search gridview records and Create Change password form in asp.net using Sql server and Stored procedure. 

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:

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 :

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."

• Tweet
• Share
• Share
• Share

Related Post