SQL Server - How to delete duplicate rows from an SQL Server Table?

Hi Everyone,

Here is the example, which illustrates how to delete the duplicate records in SQL Server?

--Create a new table EMP

CREATE TABLE EMP (EMPID INT, NAME VARCHAR(100), SALARY INT)

GO

--Insert records to the table

INSERT INTO EMP (EMPID, NAME,SALARY)

SELECT 1, 'JOHN',2000

UNION ALL

SELECT 1, 'JOHN',2000

UNION ALL

SELECT 1, 'JOHN',2000

UNION ALL

SELECT 2, 'JOHN',2000

UNION ALL

SELECT 3, 'JOHN',2000

UNION ALL

SELECT 4, 'DAVID',3000

UNION ALL

SELECT 5, 'CHARLIE',4000

UNION ALL

SELECT 6, 'HARRY',2000

GO

In this example, I want to eliminate repeated values in the NAME and SALARY columns.

--Selecting the Data

SELECT * FROM EMP

GO

--Here is the solution

WITH EMP_ROW_NUM AS

(

  SELECT

    ROW_NUM = ROW_NUMBER() OVER( PARTITION BY NAME, SALARY

    ORDER BY NAME, SALARY ), *

  FROM EMP

)

DELETE FROM EMP_ROW_NUM WHERE ROW_NUM>1

You must specify the column names that you expect duplicate values in the PARTITION BY clause. In this example, NAME and SALARY.

--Dropping the table

DROP TABLE EMP

GO

Please share this, if this helps you. Thanks for reading my Blog !