Introduction:


In this article I will explain how to delete or remove duplicate records or rows from a datatable in SQL server.
Description:
I will explain how to delete duplicate records from a datatable in SQL server. During work with one application I got requirement like get the unique records from datatable in sql server. Actually our datatable does not contain any primary key column because of that it contains duplicate records that would be like this
Actually above table does not contain any primary key column because of that same type of records exist.
Now I want to get duplicate records from datatable for that we need to write query like this
WITH tempTable as
(
SELECT ROW_NUMBER() Over(PARTITION BY Name,Position ORDER BY Name) As RowNumber,* FROM EmployeData
)
SELECT * FROM tempTable
Once we run above query we will get data like this
If you observe above table I added another column RowNumber this column is used to know which record contains duplicate values based on rows with RowNumber greater than 1.  
Now we want to get the records which contains unique value from datatable for that we need to write the query like this
WITH tempTable as
(
SELECT ROW_NUMBER() Over(PARTITION BY Name,Position ORDER BY Name) As RowNumber,* FROM EmployeData
)
DELETE FROM tempTable where RowNumber >1
SELECT * FROM EmployeData order by Id asc
Once we run above query all duplicate records will delete from our table and that would be like this

0 comments:

Post a Comment