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.
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:
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:
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
Image 4.
Now select records from your table.
Image 5.