The IT World

Introduction

Deleting duplicate records from table in SQL Server

Often, In interview it is asked that how can you delete duplicate records if there is no primary key constraint on the table. Here is some of the apporach that can help you...

create table empdetails(empid int,empname varchar(50))
drop table empdetails
insert into empdetails values('1','Kundan Kumar')
insert into empdetails values('2','Kamal Kumar')
insert into empdetails values('3','Sunil Kumar')
insert into empdetails values('4','Ramesh Kumar')
insert into empdetails values('5','Jitendra Kumar')
insert into empdetails values('6','Atul Kumar')
insert into empdetails values('7','Kunal Kumar')
insert into empdetails values('8','Suresh Verma')
insert into empdetails values('9','Vijay Kumar')
insert into empdetails values('10','Abhishek Kumar')

select * from empdetails

select distinct * from empdetails
--Create a tempTABLE and fetch all the distinct record from table having duplicate record
select distinct * into tempempdetails from empdetails
--Now Drop old table that has duplicate record
drop table empdetails
--Now Rename the tempTABLE with the name that old table had 
exec sp_rename 'tempempdetails', 'empdetails'
--Now we can check our new table
select * from empdetails
--METHOD-2
--Create a tempTABLE and fetch all the distinct record from table having duplicate record
select distinct * into tempempdetails from empdetails
--Now delete all records from old table
truncate table empdetails
--Now copy all unique records in old table from tempTABLE
insert empdetails select * from tempempdetails
--Finally drop the tempTABLE
drop table tempempdetails 

That's all....Keep Coding..Bye Bye..