--Example of Simple Duplicate Records Deletioin
-- To Create A Table Structure
Create Table Emp
(
EmpNo Int
EmpName Varchar(20)
Address Varchar(20)
City Varchar(20)
)
--Insert the Emp Table in Values
Insert Into Emp Values(1 'Fazlur Rahiman' 'Al Maktum St' 'Dubai')
Insert Into Emp Values(2 'Kalyan' 'Broad Way' 'Chennai')
Insert Into Emp Values(3 'Vijay' 'Mount Road' 'Chennai')
Insert Into Emp Values(4 'Kumar' 'Parys' 'Chennai')
Go
-- To Insert a Duplicate Records
Insert Into Emp Values(1 'Fazlur Rahiman' 'Al Maktum St' 'Dubai')
GO
--Create temp table to hold duplicate Table (#EmpTemp)
Create Table #EmpTemp
(
EmpNo Int
EmpName Varchar(20)
Address Varchar(20)
City Varchar(20)
)
--Identify and save duplicate data into #EmpTemp
Insert Into #EmpTemp
Select * From Emp
Group By EmpNo EmpName Address City
Having Count(*) > 1
--Delete duplicate from original table
Delete From Emp Where
Ltrim(Rtrim(EmpNo))+Ltrim(Rtrim(EmpName))+LTrim(Rtrim(Address))+LTrim(Rtrim(City)) in (
Select Ltrim(Rtrim(EmpNo))+Ltrim(Rtrim(EmpName))+LTrim(Rtrim(Address))+LTrim(Rtrim(City)) From emp
Group By EmpNo EmpName Address City
Having Count(*) > 1)
--Insert the delete data back
Insert Into Emp
Select * From #EmpTemp
--Check for dup data.
Select * From Emp
Group By EmpNo EmpName Address City
Having Count(*) > 1
--Check table
Select * From Emp
--Drop temp table
Drop Table #EmpTemp
