Aspdotnet-Suresh

aspdotnet-suresh offers C#.net articles and tutorials,csharp dot net,asp.net articles and tutorials,VB.NET Articles,Gridview articles,code examples of asp.net 2.0 /3.5,AJAX,SQL Server Articles,examples of .net technologies

Query to delete duplicate records or rows in sql server

May 17, 2012
Introduction:

In this article I will explain how to delete or remove duplicate records or rows from a datatable in SQL server.
Description:
In previous articles I explained about substring function in SQL server and Replace function in SQL Server and I explained how to delete duplicate records from datatable in asp.net. Now 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

If you enjoyed this post, please support the blog below. It's FREE!

Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email.

subscribe by rss Subscribe by RSS subscribe by email Subscribe by Email

17 comments :

srinivas said...

hi MR.suresh i want to know About Insteller class can u please give the demo on that u r site is so good and usefull please continue posting thank u

srinivas said...

hi MR.suresh i want to know About Insteller class can u please give the demo on that u r site is so good and usefull please continue posting thank u

Anonymous said...

Hi Suresh.. when i execute this query in sql server it gives me an error :- "Invalid column name 'RowNumber'."

Can you Please help...

Suresh Dasari said...

Here i taken RowNumber as a just columnname to display result of count i hope you made it as separate column with datatable. please check the query what i written...

Anonymous said...

I just created a table like u had. Then i ran the above mentiones query in query browser in SQL Server. The output i got is Invalid column name 'RowNumber. Can you explain this. I havent taken any datatable. i just executed the query

Anonymous said...

Got it....!!!!!

Anonymous said...

You are awesome - Very very helpful

Dorababu said...

Recently I had a question in interview as follows

For example my name is Dorababu some of them inserted as Dorababu,Dhorababu and dorebabu like this. I would like to delete the remaining except Dorababu from the table how can I do this

Anonymous said...

Dear Suresh, Is that only way to find duplicate record and delete them and is that Optimized query?

Archives said...

I don't know how to remove my date to SQL server?

André said...

AWESOME!!!!! Thanks a lot.

Nikhiil singh said...

Respected Sir,
can u tell me the best books for asp.net and sqlserver2012.

pankaj said...

Every time i read your blocks sir they are really usefull for me .........thanks a lot boss

Anonymous said...

sir can u pls tel me how to use WITH tempTable as() function in c# code

Ravinth002 said...

It's very useful for me ...........Thank u suresh

Udhaya Prakash said...

we can use like this - delete emp-table where empid
in (select empid from emp-table group by empid having count(*)>1)

Mansoor Anwar said...

I regular Visit Your Website And I Take Lot Of Use Things Here And Then Use It Own Website.
First Of All I Give Thanks To Your Efforts Regarding Your Help For New Comer In This Field.

Give your Valuable Comments

Other Related Posts

© 2010-2012 Aspdotnet-Suresh.com. All Rights Reserved.
The content is copyrighted to Suresh Dasari and may not be reproduced on other websites without permission from the owner.