Delete Duplicate rows from the table.

Delete Duplicate rows from the table.

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