Email: Password:       Forgot Password 
    .netCodeSG
A Saarsha Group Online Community for dot net codes group like C#, Asp.NET, VB.NET, Sharepoint, JavaScript, JQuery, Ajax, SQL, WCF, WPF.
 
TECHNOLOGIES:
 

Remove Duplicate Records from SQL Server Table using Common Table Expression

Article:
Viewed:  1172 
Posted On:  18/02/2015 10:15:33 
How we can Remove Duplicate Records from SQL Server Table using Common Table Expression ? 

 

In this article I am going to show how we can remove duplicate records from a SQL server table.

For Exp:  I have below SQL Server table in my Data Base.

1.png

Image 1.

Script of my table is:

CREATE TABLE [dbo].[Employee](
          [Emp_ID] [int] IDENTITY(1,1) NOT NULL,
          [Name] [varchar](50) NULL,
          [Email] [varchar](500) NULL,
          [Designation] [varchar](50) NULL,
          [City] [varchar](50) NULL,
          [State] [varchar](50) NULL,
          [Country] [varchar](50) NULL) ON [PRIMARY] 

GO

 

Now I have some records in my table:

2.png

Image 2.

Here you can see I have some duplicate records in this table.

Now the challenge is how we can remove these duplicate records using SQL statement.

So follow below statements:

WITH CTE AS(
SELECT ROW_NUMBER() OVER(PARTITION BY NAME ORDER BY NAME) AS ROWNO,
                                                NAME,Emp_ID, Designation, Email FROM Employee
)
SELECT * FROM CTE WHERE ROWNO > 1

 

Above statement return all duplicate records with number of occurrence:

3.png

Image 3.

Now use below statements to remove duplicate records.

WITH CTE AS(
SELECT ROW_NUMBER() OVER(PARTITION BY NAME ORDER BY NAME) AS ROWNO,
                                                NAME,Emp_ID, Designation, Email FROM Employee
)
DELETE FROM CTE WHERE ROWNO > 1

 

4.png

Image 4.

Now select records from your table.

5.png

Image 5.

  Comment:
         HOME   |   Submit Article   |   Contact Us   |   About Us   |   Terms & Condition   |   Advertise With us