dotnetspider.com
Login Login    Register      

TutorialsForumCareer DevelopmentResourcesReviewsJobsInterviewCommunitiesProjectsTraining

Subscribe to Subscribers
Talk to Webmaster
Tony John

Facebook
Google+
Twitter
LinkedIn
Online Membersnaveensanagasetti
Sunil Jas
penchala
gopal
Danasegarane.A
Pawan Awasthi
More...
Join our online Google+ community for Bloggers, Content Writers and Webmasters




Resources » SQL Server

How to delete duplicate record from SQL Server table?


Posted Date:     Category: SQL Server    
Author: Member Level: Diamond    Points: 25


In this article I am going to explain about how to delete duplicate record from sql server table. I am using temp table concept to remove duplicate same data from sql server table.



 


Description


In SQL server sometimes there are duplicate records are inserted with same details. That time we need to remove all duplicate rows from table but we don't have any primary key field in that table. In that situation apply below temp table concept to find out duplicate rows and delete it from table.

Table structure



create table emp(eno int,empname varchar(50),sal bigint)

Insert table data with duplication



insert into emp values('101','James','45000')
insert into emp values('101','James','45000')
insert into emp values('102','mike','12000')
insert into emp values('102','mike','45000')
insert into emp values('102','mike','45000')
insert into emp values('103','Andrew','12000')

Find duplicate Rows


1) Find using simple query

SELECT empname, COUNT(*) FROM emp GROUP BY empname HAVING COUNT(*) > 1


2) Find using temp table

WITH tempTable as
(
SELECT ROW_NUMBER() Over(PARTITION BY eno,empname ORDER BY eno) As RowNumber,* FROM emp
)

select * from tempTable where RowNumber >1


Delete duplicate Rows



WITH tempTable as
(
SELECT ROW_NUMBER() Over(PARTITION BY eno,empname ORDER BY eno) As RowNumber,* FROM emp
)

DELETE FROM tempTable where RowNumber >1


Conclusion


I hope above process is help you to find about duplicate rows and delete duplicate rows from sql server table.





Did you like this resource? Share it with your friends and show your love!


Responses to "How to delete duplicate record from SQL Server table?"
Author: Ajesh Madhukar Dalvi    13 Aug 2012Member Level: Silver   Points : 4
rno sname
—————-
1 Frieda
1 Frieda
1 Frieda
4 Nathan
6 senthil
6 senthil
6 senthil
2 Senthil
6 senthil
1 Shanoj
2 Shanoj
4 Varun

Step 1: Create duplicate table and move the duplicate value records of the original table to a duplicate table.

SELECT DISTINCT * INTO DuplicateTb FROM dublicatetest GROUP BY sname,rno HAVING COUNT(rno) > 1

After executed the above Query if you look the table the below result come.
select * from DuplicateTb
1 Frieda
6 senthil

select * from dublicatetest

2 Shanoj
1 Frieda
2 Senthil
4 Varun
6 senthil
1 Shanoj
6 senthil
4 Nathan
6 senthil
6 senthil
1 Frieda
1 Frieda

Step 2: Delete all rows from the original table that also reside in the duplicate table.

DELETE dublicatetest WHERE sname IN (SELECT sname FROM DuplicateTb)

After executed the above Query if you look the table the below result come.

select * from DuplicateTb
1 Frieda
6 senthil

select * from dublicatetest
2 Shanoj
4 Varun
1 Shanoj
4 Nathan

Step 3 : Move back the rows from duplicate table to original table.

INSERT dublicatetest SELECT * FROM DuplicateTb

After executed the above Query if you look the table the below result come.

select * from DuplicateTb
1 Frieda
6 senthil

select * from dublicatetest
2 Shanoj
1 Frieda
6 senthil
4 Varun
1 Shanoj
4 Nathan

Step4: Drop the duplicate table.

DROP TABLE DuplicateTb



Author: nuwan rathnayake    23 Aug 2012Member Level: Silver   Points : 0
It work. Nice one........


Feedbacks      

Post Comment:




  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:   Sign In to fill automatically.
    Email: (Will not be published, but required to validate comment)



    Type the numbers and letters shown on the left.


    Next Resource: What is SET NOCOUNT ON in SQL Server?
    Previous Resource: Debug your stored procedure
    Return to Resources
    Post New Resource
    Category: SQL Server


    Post resources and earn money!
     
    More Resources
    Popular Tags   Tag posting guidelines   Search Tags  
    Delete duplicate record  .  



    Follow us on Twitter: https://twitter.com/dotnetspider

    Active Members
    TodayLast 7 Daysmore...

    Awards & Gifts
    Email subscription
  • .NET Jobs
  • .NET Articles
  • .NET Forums
  • Articles Rss Feeds
    Forum Rss Feeds


    About Us    Contact Us    Copyright    Privacy Policy    Terms Of Use    Revenue Sharing sites   Advertise   Talk to Tony John
    Copyright © SpiderWorks Technologies Pvt Ltd., Kochi, India
    2005 - 2012 All Rights Reserved.
    .NET and other trademarks mentioned in this site belong to Microsoft and other respective trademark owners.
    Articles, tutorials and all other content offered here is for educational purpose only.
    We are not associated with Microsoft or its partners.