SQL Server | Remove Duplicate Records From Table Using CTE

watch_later 1/21/2023

how to delete duplicate records in sql server,sql delete duplicate rows but keep one,how to delete duplicate rows in oracle,delete duplicate rows in sql,how to delete duplicate records in mysql,elete duplicate rows in sql w3schools,to remove duplicate rows in sql select statement,sql remove duplicates based on two columns,delete duplicate records in sql server using row_number,how to delete duplicate rows in sql,delete duplicate rows in sql w3schools,sql delete duplicate rows but keep one,how to delete duplicate rows in oracle,sql remove duplicates from ( select),how to delete duplicate rows in sql using rowid,how to delete duplicate records in mysql

In this article, I am going to explain how to delete duplicate records in an SQL server. I'll also explain how you can delete/remove duplicate records from the table with Common Table Expression in the SQL server. And also show you how you can find duplicate records from the table in the SQL server as well as what is CTE(Common Table Expression) and how to use CTE(Common Table Expression) in SQL Server.

In my previous article, I explained How to Remove Duplicate Records From a Table Using UNION And EXCEPT Operator in SQL Server with an example.

What is CTE(Common Table Expression) in SQL Server?

In SQL Server CTE(Common Table Expression) is introduced in SQL Server 2005, and it is used to create a recursive query. It is a temporary named result set that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement as well as it is preferred to use as Subquery/View.

In SQL server view is a virtual table based on the result set of an SQL statement and Subquery is a query within the query and we also can say it is an inner/nested query.

Syntex

;With Alias_Tablename (column1,column2...,columnsN)
 
AS
 
( Your Query )

Types of Common Table Expressions (CTE) in SQL Server

In SQL Servers Common Table Expressions (CTE) have two different types recursive and non-recursive. Where recursive CTE can be explained in three different parts  Anchor Query, Separator, and Recursive Query.

What is Anchor Query?

Anchor Query will give the base data for the CTE and this is the first statement that is executed.

What Is Separator?

This is the middle part wherein we generally use an EXCEPT, UNION, UNION ALL, and a few more operators.

What is Recursive Query?

Recursive Query is the Common Table Expression(CTE) query that refers to the same CTE by recursion, and this is the main part of recursive CTE.

Requirement

  • Create a temporary with some dummy records.
  • Insert duplicate records in the created temporary table.
  • Remove duplicate records from the created table using CTE(Common Table Expressions).

Implementation

As per requirement first, we will create/declare a temporary table with the name "tblEmployees" in the SQL server.

DECLARE  @tblEmployees AS TABLE
(
  EmpId int NOT NULL PRIMARY KEY,
  CompanyId int NOT NULL,
  FirstName varchar(50) NOT NULL,
  LastName varchar(50) NOT NULL,
  Designation varchar(50) NOT NULL,
  Country varchar(50) NOT NULL,
  Date_Of_Birth DATETIME NOT NULL
)

Now, we will insert some dummy duplicate records on the table for demonstration purposes.

INSERT INTO @tblEmployees VALUES (1,10798, 'Nikunj', 'Satasiya', 'Sr.Software Engineer','India','1996-04-08 00:00:00.000')
INSERT INTO @tblEmployees VALUES (2,10798, 'Nikunj', 'Satasiya', 'Sr.Software Engineer','India','1996-04-08 00:00:00.000')
INSERT INTO @tblEmployees VALUES (3,10798, 'Hiren', 'Dobariya', 'Sr.Software Engineer','India','1996-03-10 00:00:00.000')
INSERT INTO @tblEmployees VALUES (4,10798, 'Ronak', 'Rabadiya', 'Sr.Software Engineer','India','1993-04-09 00:00:00.000')
INSERT INTO @tblEmployees VALUES (5,10798, 'Hiren', 'Dobariya', 'Sr.Software Engineer','India','1996-03-10 00:00:00.000')
INSERT INTO @tblEmployees VALUES (6,10798, 'Nikunj', 'Satasiya', 'Sr.Software Engineer','India','1996-04-08 00:00:00.000')
INSERT INTO @tblEmployees VALUES (7,10798, 'Sneha', 'Patel', 'Support Executive','India','1995-02-12 00:00:00.000')
INSERT INTO @tblEmployees VALUES (8,10798, 'Sheetal', 'Patel', 'Web Designer','India','1995-02-12 00:00:00.000')
INSERT INTO @tblEmployees VALUES (9,10798, 'Ronak', 'Rabadiya', 'Sr.Software Engineer','India','1993-04-09 00:00:00.000')
INSERT INTO @tblEmployees VALUES (10,10798, 'Ronak', 'Patel', 'Sr.Software Engineer','India','1995-11-12 00:00:00.000')

If you execute the select statement and retrieve the result set from the @tblEmployees table then you found there is duplication in the @tblEmployees table.

SELECT * FROM @tblEmployees ORDER BY EmpId

Result Set

SQL Server Duplicate Records
SQL Server Duplicate Records

As per our requirement, we will remove these duplicate entries/records from the @tblEmployees table with CTE(Common Table Expression).

;WITH MyEmployees (FirstName,DuplicateCount)
AS
(
-- Generate RowNumber based on Employee FirstName, LastName Wise
   SELECT FirstName,ROW_NUMBER() OVER(PARTITION by FirstName, LastName ORDER BY EmpId)
   AS DuplicateCount
   FROM @tblEmployees
)
--Remove Duplicate Records From MyEmployees (Common Table Expressions)
DELETE FROM MyEmployees WHERE DuplicateCount > 1

If you analyzed the above SQL statement then I have declared one Common Table Expressions(CTE) with the name "MyEmployees". As per the syntax of CTE, I have written a query where I have generated a row number partition by Employee First and Last Name order by Employee Identity and give an alias to the column of row number as DuplicateCount.

Finally, I deleted these rows from the CTE table "MyEmployees" where DuplicateCount is greater than 1.

Now, If you execute the select statement and retrieve the result set from the @tblEmployees table, you will find no duplication in the @tblEmployees table and all the duplicate records/rows are removed from your base table @tblEmployees.

SELECT * FROM @tblEmployees ORDER BY EmpId

Result Set

Sql Server Remove Duplicate Records
SQL Server Remove Duplicate Records


The advantage of CTE(Common Table Expression)

  • In SQL Server Common Table Expression(CTE) improves your code readability.
  • Common Table Expression(CTE) provides recursive programming.
  • CTE is making the maintenance of complex queries easy.
  • It makes code maintainability easier.
  • It can be used in stored procedures, functions, triggers as well as even views also.
  • CTE can be used as a Table or a View and we can perform SELECT, INSERT, UPDATE or DELETE operations on the table.

Summary

SQL Server CTE(Common Table Expression) creates a recursive query. It is a temporary named result set that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement as well as it is preferred to use as Subquery/View and it improves the code readability, maintenance of complex queries, code maintainability and provides recursive programming.

Codingvila provides articles and blogs on web and software development for beginners as well as free Academic projects for final year students in Asp.Net, MVC, C#, Vb.Net, SQL Server, Angular Js, Android, PHP, Java, Python, Desktop Software Application and etc.

Thank you for your valuable time, to read this article, If you like this article, please share this article and post your valuable comments.

Once, you post your comment, we will review your posted comment and publish it. It may take a time around 24 business working hours.

Sometimes I not able to give detailed level explanation for your questions or comments, if you want detailed explanation, your can mansion your contact email id along with your question or you can do select given checkbox "Notify me" the time of write comment. So we can drop mail to you.

If you have any questions regarding this article/blog you can contact us on info.codingvila@gmail.com

sentiment_satisfied Emoticon