Delete duplicate records in a table

--Using CTE (common table expression)

CREATE TABLE REM_DUP_DATA(ID INT,NAME VARCHAR(10))

INSERT INTO REM_DUP_DATA VALUES

(1,'FIRST'),(1,'FIRST'),(1,'FIRST'),

(2,'SECOND'),(2,'SECOND'),

(3,'THIRD')

SELECT * FROM REM_DUP_DATA

Output:

ID          NAME

----------- ----------

1           FIRST

1           FIRST

1           FIRST

2           SECOND

2           SECOND

3           THIRD

WITH CTE (ID,NAME,DuplicateCount)

AS

(

SELECT ID,NAME,ROW_NUMBER() OVER(PARTITION BY ID,NAME ORDER BY ID,NAME) AS DuplicateCount

FROM REM_DUP_DATA

)

DELETE

FROM CTE

WHERE DuplicateCount > 1

SELECT * FROM REM_DUP_DATA

Result:

ID          NAME

----------- ----------

1           FIRST

2           SECOND

3           THIRD

--Alternate method

--insert distinct records into a temporary table

SELECT DISTINCT * INTO REM_DUP_DATA_TMP

FROM REM_DUP_DATA

SELECT * FROM REM_DUP_DATA_TMP

Output:

ID          NAME

----------- ----------

1           FIRST

2           SECOND

3           THIRD

--truncate your main table

TRUNCATE TABLE REM_DUP_DATA

--after truncate re-insert records from REM_DUP_DATA_TMP

INSERT INTO REM_DUP_DATA

SELECT * FROM REM_DUP_DATA_TMP

SELECT * FROM REM_DUP_DATA

Result

ID          NAME

----------- ----------

1           FIRST

2           SECOND

3           THIRD