Sunday, March 31, 2019

Azure Storage Account Part 1 & 2 Codeproject

Thursday, March 28, 2019

Delete Duplicate Rows in SQL Server using Common Table Expression

Introduction:

Sometimes, some duplicate data may be inserted in the SQL table. But it's harmful to you and your application too. So how do you remove the duplicate rows in your table?

Description

Take a look at the following table content. It has duplicate rows.

IdNameDepartment
1Arka.NET
2Ashok.NET
3AnubhavPHP
4Arka.NET
5SudeshnaGraphics

Row 1 and 4 are duplicates. So we have to remove one row and keep one in the table. Now the question is how to do this?

Using the Code

To solve this problem, take a look at the following code. This will solve this problem.

WITH tblTemp as
(
SELECT ROW_NUMBER() Over(PARTITION BY Name,Department ORDER BY Name)
   As RowNumber,* FROM <table_name>
)
DELETE FROM tblTemp where RowNumber >1

After running, this code shows the table data, it will ensure that all unique rows are present.

Follow by Email

Popular Posts

Pageviews