Beyond Web Logs

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

Technorati : duplicate, sql server, table
Del.icio.us : duplicate, sql server, table
Zooomr : duplicate, sql server, table
Flickr : duplicate, sql server, table

SQL duplicate, sql server, table