How to Delete Duplicate records from Table in Sql Server ~ MiTechDev.com

Mitechdev.com is a programming blog which focused on Web Programming and development

Wednesday, 28 June 2017

How to Delete Duplicate records from Table in Sql Server

03:47:00 Posted by raviteja swayampu No comments
In this post i will  show you how to delete or eliminate redundant(duplicate) rows with same data(including id column also).I will demonstrate this using  a simple example..
1.Create a table with below schema..and insert some data...
Employee.sql
CREATE TABLE [dbo].[Employee](
 [EmpId] [int] NULL,
 [EmpName] [varchar](50) NULL,
 [EmpMobi] [int] NULL
) ON [PRIMARY]

INSERT INTO Employee(EmpId,EmpName,EmpMobi) VALUES(1,'Varma',954455)
INSERT INTO Employee(EmpId,EmpName,EmpMobi) VALUES(2,'Gopal',783493)
INSERT INTO Employee(EmpId,EmpName,EmpMobi) VALUES(3,'Ragav',123456)
INSERT INTO Employee(EmpId,EmpName,EmpMobi) VALUES(3,'Ragav',123456)
INSERT INTO Employee(EmpId,EmpName,EmpMobi) VALUES(3,'Ragav',123456)
INSERT INTO Employee(EmpId,EmpName,EmpMobi) VALUES(4,'Suresh',984357)

2.Now i have 3 records with duplicate details.Now my task is to remove to two records from three.It is not possible to delete using 'delete' command because it requires one column value.
3.If i apply delete command all three records will be deleted.but,i need one record(i want to remove duplicate records only).
3.I created a temporary Object and stored above table in it..
select * into #temp from Employee

select * from #temp

4.Now run the below query to remove the duplicate records from the Temporary Table..

; with cte
as(

select * ,ROW_NUMBER() over (partition by EmpId,EmpName,EmpMobi order by EmpId)as rownumber from #temp
)
delete from cte where rownumber>1
5.We will get a table without duplicate records like below after executing above query..


0 comments:

Post a Comment