Total Page Preview:   000000005853

SQL SERVER- Delete-Remove duplicate record or rows from table in sql server

In this article I am going to explain How to delete - Remove duplicate record or rows from table in sql server with example. Sometimes you may want to delete all duplicate recoeds - rows in table. So I am explaining details with live example:
 
EmppId EmpName Salary  
1 ithubcity 1000  
1 ithubcity 1000 Duplicate row
1 ithubcity 1000 Duplicate row
2 blog 500  
2 blog 500 Duplicate row
3 blog 522  
 
 
Delete/Romove Duplicate record/rows Using Common Table Expression (CTE):
 
-----------------Delete/Romove duplicate row/records Using CTE ------------------------
;WITH CTEEmployee AS
(
       SELECT EmppId, EmpName, Salary,
       ROW_NUMBER() OVER(PARTITION BY  EmppId, EmpName, Salary ORDER BY EmppId) AS 'RowNumber'
       FROM tblEmployee
)
DELETE FROM CTEEmployee WHERE RowNumber > 1
 
 
 
Delete/Romove Duplicate record/rows Using Sub Query:
 
-----------------Delete - Remove duplicate row/records Using Sub Query ------------------------
 
DELETE  Employee FROM (
                                             SELECT  EmppId, EmpName, Salary,
                                             ROW_NUMBER() OVER (PARTITION BY  EmppId, EmpName, Salary ORDER BY EmppId) AS 'RowNumber'
                                             FROM     [dbo].[tblEmployee]
                                                  ) AS Employee
WHERE   RowNumber > 1
 
 
Get SQL Scripts for Create Tables and Insert Data for demo:
 
---------------Create table ------------
CREATE TABLE [dbo].[tblEmployee]
(
      [EmppId] [int] NULL,
      [EmpName] [varchar](50) NULL,
      [Salary] [float] NULL
) ON [PRIMARY]
 
--------------------------------- Insert record into table------------------------
INSERT [dbo].[tblEmployee] ([EmppId], [EmpName], [Salary]) VALUES (1, N'ithubcity', 100)
INSERT [dbo].[tblEmployee] ([EmppId], [EmpName], [Salary]) VALUES (1, N'ithubcity', 100)
INSERT [dbo].[tblEmployee] ([EmppId], [EmpName], [Salary]) VALUES (1, N'ithubcity', 100)
INSERT [dbo].[tblEmployee] ([EmppId], [EmpName], [Salary]) VALUES (1, N'ithubcity', 100)
INSERT [dbo].[tblEmployee] ([EmppId], [EmpName], [Salary]) VALUES (2, N'blog', 500)
INSERT [dbo].[tblEmployee] ([EmppId], [EmpName], [Salary]) VALUES (2, N'blog', 500)
INSERT [dbo].[tblEmployee] ([EmppId], [EmpName], [Salary]) VALUES (3, N'blog', 522)
 
 
 
Thanks !!!
Brijesh Kumar
 

Thank You

About Author

Brijesh Kumar

Database Developer

I have more then 6 years Experience in Microsoft Technologies - SQL Server Database, ETL Azure Cloud - Azure SQL Database, CosmosDB, Azure Data Factory, PowerBI, Web Job, Azure Function, Azure Storage, Web Apps, Powershall and Database Migration On-Premise to Azure Cloud.
LinkedIn : https://www.linkedin.com



Comments

Chong
27-Mar-2019
Great article. I will be going through many of these issues as well..
Caldwell
04-Apr-2019
Everything is very open with a very clear clarification of the issues. It was truly informative. Your website is very useful. Thank you for sharing!
Fortin
28-Mar-2019
It's perfect time to make some plans for the longer term and it's time to be happy. I have read this publish and if I may just I wish to recommend you some attention-grabbing things or advice. Maybe you can write subsequent articles relating to this article. I wish to learn even more issues about it!
Selfe
04-Aug-2019
Hello to every body, it's my first visit of this website; this webpage consists of amazing and in fact excellent material designed for visitors.
navjot
17-Nov-2016
Good Explanation!!!Keep it up:)
Toussaint
12-Nov-2019
Inspiring quest there. What occurred after? Good luck!
Rankin
21-Nov-2019
I've been surfing online more than three hours today, yet I never found any interesting article like yours. It's pretty worth enough for me. In my view, if all site owners and bloggers made good content as you did, the internet will be much more useful than ever before.
Bertram
21-Nov-2019
Hey there! This is my first comment here so I just wanted to give a quick shout out and say I really enjoy reading your posts. Can you recommend any other blogs/websites/forums that deal with the same subjects? Thanks for your time!
Banner
16-Nov-2019
Attractive section of content. I just stumbled upon your weblog and in accession capital to assert that I acquire actually enjoyed account your blog posts. Anyway I will be subscribing to your augment and even I achievement you access consistently fast.
Whitefoord
20-Nov-2019
First of all I would like to say wonderful blog! I had a quick question that I'd like to ask if you don't mind. I was interested to find out how you center yourself and clear your head prior to writing. I've had difficulty clearing my mind in getting my ideas out there. I do enjoy writing however it just seems like the first 10 to 15 minutes are usually lost simply just trying to figure out how to begin. Any ideas or hints? Thank you!
Ciantar
12-Nov-2019
Hi mates, fastidious article and good urging commented here, I am genuinely enjoying by these.
Mattocks
16-Nov-2019
Hmm is anyone else encountering problems with the pictures on this blog loading? I'm trying to figure out if its a problem on my end or if it's the blog. Any feed-back would be greatly appreciated.
Duras
22-Nov-2019
You have made some decent points there. I looked on the web to learn more about the issue and found most people will go along with your views on this site.

                           
                           

                           

                           

Facebook User: