--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
