Total Page Preview:   000000004019

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!
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.
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.

                           
                           

                           

                           

Facebook User: