How To Delete Duplicate Records in SQL Server

--Table with Duplicate Records

SELECT * FROM EMPLOYEES

-- Temporary Table to store Duplicate Records only

DECLARE @TEMP TABLE(

                        REC_ID INT IDENTITY(1,1),

                        FIRST_NAME NVARCHAR(50),

                        LAST_NAME NVARCHAR(50),

                        REC_COUNT INT) 

--Finding Duplicate records that are presented more than once

INSERT INTO @TEMP

SELECT      FIRST_NAME,

            LAST_NAME,

            COUNT(*)AS REC_COUNT

FROM EMPLOYEES

GROUP BY FIRST_NAME,LAST_NAME

HAVING COUNT(*)>1

-- Displaying the duplicate records with no of occurrences

SELECT * FROM @TEMP

DECLARE @DUP_REC_COUNT INT,

            @REC_COUNT INT,

            @FIRST_NAME VARCHAR(100),

            @LAST_NAME VARCHAR(100)

SELECT @DUP_REC_COUNT = COUNT(*) FROM @TEMP

-- Deleting Duplicate records one by one

WHILE @DUP_REC_COUNT>0

BEGIN

      SELECT      @FIRST_NAME = FIRST_NAME,

                  @LAST_NAME=LAST_NAME,

                  @REC_COUNT = REC_COUNT

      FROM  @TEMP

      WHERE REC_ID=@DUP_REC_COUNT 

      -- Deleting all duplicates except one record

      DELETE FROM EMPLOYEES

      WHERE EMI_ID IN (

                  SELECT TOP (@REC_COUNT-1) E1.EMI_ID

                  FROM EMPLOYEES E1

                  WHERE E1.FIRST_NAME = @FIRST_NAME

                        AND E1.LAST_NAME=@LAST_NAME)

      SET @DUP_REC_COUNT = @DUP_REC_COUNT-1

END

-- Table after deleting Duplicate records

SELECT * FROM EMPLOYEES