kamagra kart

Monday, November 24, 2014

Delete duplicate rows from table

November 1, 2009 by · 1 Comment 

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 duplicate records except one Primary key for each duplicate record.

Top Blogs

Comments

One Response to “Delete duplicate rows from table”
  1. nejutiouffink says:

    Very Good site, thank yo mister, it’s help’s me!

Speak Your Mind

Tell us what you're thinking...
and oh, if you want a pic to show with your comment, go get a gravatar!

*