Delete Duplicate rows from the table.

Article posted by Deysomnath on 1/28/2008 | Views: 7964 | Category: Sql Server | Level: Intermediate

Submit Article |

Search Articles |

Articles Home |

Suppose there is a table called "EmployeeTable" which have some duplicate records.
There is a three way to delete the duplicate rows.

First way to delete duplicate rows :

Select distinct * into Emp_Temp_Table from EmployeeTable

In the above line we are inserting all the distinct row of the "EmployeeTable" to another table "Emp_Temp_Table" (Emp_Temp_Table will create automatically when you use the above query.)
Actuall the above query create clone of EmployeeTable and insert all the distinct row inside the Clone Table (Emp_Temp_Table).

drop table EmployeeTable

sp_rename 'Emp_Temp_Table',EmployeeTable'

Then Delete the orginal table and rename the clone table with the name of orginal table.

Second way to delete duplicate rows :

Select distinct * into Emp_Temp_Table from EmployeeTable

Truncate table EmployeeTable

insert into EmployeeTable select * from Emp_Temp_Table

drop table Emp_Temp_Table

Third way to delete duplicate rows :

Populate the new Primary Key

Alter table EmployeeTable add NewPK int NULL
Go
Declare @intCounter int
Set @intCounter = 0
Update EmployeeTable
SET @intCounter = NewPK = @intCounter + 1

Select name,RecCount=count(*), PktoKeep = max(NewPK)
Into #dupes
From EmployeeTable
Group by name
Having count(*) > 1
Order by count(*) desc

Delete dupes except one Primary key for each dup record

Delete test
from EmployeeTable a join #dupes d
a.name
where a.NewPK not in (select PKtoKeep from #dupes)

Remove the NewPK column

ALTER TABLE test DROP COLUMN NewPK
go

drop table #dupes

If you like this article, subscribe to our RSS Feed. You can also subscribe via email to our Interview Questions, Codes and Forums section.

Found interesting? Add this to:

| More | Bookmark It

Please Sign In to vote for this post.

Latest Articles from Deysomnath

◘ Detailed Steps to create Application Pool

◘ A Guide to SQL Server Indexes

◘ Best Practices to Improve ASP.Net Web Application Performance.

◘ Delete Duplicate rows from the table.

◘ Metadata Vs Manifest

About Somnath Dey

Experience:	5 year(s)
Home page:	http://somnathdey.blogspot.com/
Member since:	Tuesday, April 29, 2008
Level:	Starter
Status:	[Member]
Biography:	As soon as the fear approaches near, attack and destroy it.

Responses

Posted by: Sirish79 | Posted on: 13 Apr 2009 06:09:43 AM

These are some other ways for performing the same operation.

CREATE TABLE #duplicateTest
(
[ID] [int] ,
[FirstName] [varchar](25),
[LastName] [varchar](25)
) ON [PRIMARY]

INSERT INTO #duplicateTest VALUES(1, 'Bob','Smith')
INSERT INTO #duplicateTest VALUES(2, 'Dave','Jones')
INSERT INTO #duplicateTest VALUES(3, 'Karen','White')
INSERT INTO #duplicateTest VALUES(1, 'Bob','Smith')

Select * from #duplicatetest

SELECT * FROM #duplicatetest WHERE ID = 1 AND FirstName = 'Bob' AND LastName = 'Smith'

SET ROWCOUNT 1
DELETE FROM #duplicatetest WHERE ID = 1
SET ROWCOUNT 0

Select * from #duplicatetest
DELETE TOP(1) FROM #duplicatetest WHERE ID = 1
Select * from #duplicatetest

Create Table #Emptest
(
EmpID int,
Empname varchar(20)
)

Insert into #EmpTest values('1','Aufaq')
Insert into #EmpTest values('1','Aufaq')
Insert into #EmpTest values('1','Aufaq')
Insert into #EmpTest values('1','Aufaq')
Insert into #EmpTest values('1','Aufaq')
Insert into #EmpTest values('1','Aufaq')
Insert into #EmpTest values('1','Aufaq')
Insert into #EmpTest values('1','Aufaq')
Insert into #EmpTest values('1','Aufaq')
Insert into #EmpTest values('1','Aufaq')

Insert into #EmpTest values('2','Bashaerat')
Insert into #EmpTest values('2','Bashaerat')
Insert into #EmpTest values('2','Bashaerat')
Insert into #EmpTest values('2','Bashaerat')
Insert into #EmpTest values('2','Bashaerat')
Insert into #EmpTest values('2','Bashaerat')
Insert into #EmpTest values('2','Bashaerat')
Insert into #EmpTest values('2','Bashaerat')
Insert into #EmpTest values('2','Bashaerat')
Insert into #EmpTest values('2','Bashaerat')

Insert into #EmpTest values('3','John')
Insert into #EmpTest values('3','John')

DELETE TOP (SELECT COUNT(*) -1 FROM #EmpTest WHERE Empname = 'Bashaerat')
FROM #EmpTest
WHERE Empname = 'Bashaerat'
Select * from #EmpTest

>> Write Response - Respond to this post and get points

Latest Articles

◘ App Innovation Winner Brings Speech to the Silent

◘ Find the Factorial of number(s) in SQL Server

◘ Find Second highest salary for each department in SQL Server

◘ Let us learn ROLLUP function in SQL Server

◘ Road Construction - Solution for Coding Challenge by Capgemini

◘ Let us learn the Compose and Decompose function of Oracle

◘ Let us know about Ratio_To_Report Function in Oracle

◘ Small Project on How to use the Chart Control in ASP.NET?

◘ Custom Style Validation in Asp.net MVC3

More ...

SQL Server Integration Services (SSIS) – Part 33 – Aggregate (SUM) Transformations Control in SSIS

This is part 33 of the series of article on SSIS. In this article we are going to see on how to use an Aggregate (SUM) data flow transformation control in SSIS packaging.

Common Table Expressions in SQL Server

In one of my recent project works, I was assigned to deliver the silverlight 2.0 solution to display Organization Chart. I was required to represent the flat or relational data into a hierarchy. Though there are number of traditional ways of achieving the same. I wanted to do something new which can increase the weightage of my decision to use Microsoft Technologies.

SQL Server Interview Questions

In this article we will go through the most basic and frequently asked interview questions on SQL Server. Please do not think that I am writing this article to show shortcuts to candidates who are searching for jobs on SQL Server. But I think no matter how much experience you have, an interview is a different ball game. A good project executioner can get knocked off on simple SQL Server questions.

Let's learn Percent Rank function of SQL Server 2012 (Denali)

In this article, we will look into the Percent Rank function of SQL Server 2012 (Denali).

Let's learn Concat Function in Sql Server 2012

In this article, we will learn Concat Function in Sql Server 2012

More ...