Duplicates Row Detection :
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
Only Selected Duplicate Row
Duplicate row select only …………………………………………
SELECT name, roll, count( roll) AS sroll FROM student
GROUP BY name HAVING sroll >1
Data Migration One table to another table
insert into student_details(sid , sname, dept_id) select s.roll , s.name ,s.dept_id from student s, departments d where s.dept_id = d.id
Or
Insert into student_details(sid, sname, dept_id) select s.roll, s.name, s.dept_id from student s inner join department d oN s.dept_id = d.id
ahsan's weblogs 