Total Page Preview:   000000009649

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.
Fyans
08-Aug-2020
I like looking through a post that can make people think. Also, thank you for allowing me to comment!
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.
Isaac
11-Jan-2021
Its lіke y᧐u read my mind! Υoᥙ appear to know a lot about this, like yоu wrote the book іn it oг sοmething. I tһink that you can do with some pics to drive the message һome ɑ lіttle bit, Ƅut other than that, thіs is magnificent blog. Ꭺ fantastic read. I'll certaіnly be baсk.
Virgin
15-Nov-2020
Hurrah! Finally I got a website from where I know how to in fact obtain helpful data concerning my study and knowledge.
Henning
08-Aug-2020
Amazing! This blog looks exactly like my old one! It's on a totally different subject but it has pretty much the same layout and design. Great choice of colors!
Yang
09-Aug-2020
I am truly thankful to the owner of this web page who has shared this fantastic post at at this place. adreamoftrains webhosting
Catlett
08-Nov-2020
Hello, of course this article is actually good and I have learned lot of things from it on the topic of blogging. thanks.
Steadham
28-Apr-2021
If some one desires to be updated with latest technologies then he must be pay a quick visit this website and be up to date every day.
Loos
27-Apr-2021
Howdy just wanted to give you a quick heads up. The words in your content seem to be running off the screen in Ie. I'm not sure if this is a formatting issue or something to do with browser compatibility but I thought I'd post to let you know. The design look great though! Hope you get the problem solved soon. Cheers
Messenger
23-Aug-2021
Hello, I wish for to subscribe for this weblog to obtain hottest updates, so where can i do it please help.
Akers
24-Aug-2021
Piece of writing writing is also a fun, if you be familiar with after that you can write otherwise it is complex to write.
Keeler
24-Aug-2021
Great post however I was wanting to know if you could write a litte more on this subject? I'd be very grateful if you could elaborate a little bit more. Cheers!
Laborde
31-Aug-2021
Everyone loves it when folks come together and share opinions. Great blog, keep it up!
Morris
14-Sep-2021
Hey, I think your blog might be having browser compatibility issues. When I look at your blog in Safari, it looks fine but when opening in Internet Explorer, it has some overlapping. I just wanted to give you a quick heads up! Other then that, amazing blog!
Crespin
15-Nov-2020
I don't even know how I finished up here, however I assumed this submit was once good. I do not know who you are however definitely you're going to a well-known blogger in the event you are not already. Cheers!
Pontius
30-Apr-2021
I am regular reader, how are you everybody? This piece of writing posted at this web page is really pleasant.
Mattingly
01-May-2021
With havin so much content and articles do you ever run into any problems of plagorism or copyright violation? My site has a lot of exclusive content I've either authored myself or outsourced but it appears a lot of it is popping it up all over the web without my authorization. Do you know any techniques to help reduce content from being ripped off? I'd certainly appreciate it.

                           
                           

                           

                           

Recent Posts

How to get the alternate rows or records from table in sql server
07-Jun-2017
Data Warehouse Three Tier Architecture
20-Aug-2017
SQL Server - Fragmentation on azure sql database
07-Oct-2018
Limitations of Azure SQL Database
19-Sep-2019
PIVOT and UNPIVOT in SQL Server with Example
29-Jun-2017
SQL Server - Find all index with key columns on Azure SQL Database
02-Oct-2018
SQL Server - Find unused indexes on azure SQL database scripts
02-Oct-2018
Find Resource usage in Azure SQL Database using script or T-SQL
07-Oct-2018
How to Set client IP Address using the firewall security in Azure SQL Using Portal
29-Jun-2017
What is a Trigger and type of trigger in SQL server?
10-Sep-2021
What is Table Variable? Explain its advantages and disadvantages?
10-Sep-2021
Top 60 SQL Server Interview Questions Answers for freshers and experienced
17-Sep-2021
Cloud Data Engineer SQL Server Interview Questions Answers for freshers and experienced
31-Aug-2021
Top 16 performance query optimization techniques in SQL Database?
16-Sep-2021
Data warehouse Interview Questions Answers for freshers and experienced
16-Sep-2021
What are the types of User Define Function and difference between a nested UDF and recursive UDF in SQL server?
10-Sep-2021
What are the different types of Error Handling in SQL Server?
11-Sep-2021
What are the differences between OLTP and OLAP?
07-Sep-2021
How many types of dimension tables available in data warehouse?
16-Sep-2021
What are characteristics and advantages of stored procedure?
10-Sep-2021

Facebook User: