Monday, December 11, 2017

How to find duplicate records from sql server Table and Delete

How to find the duplicate record ?

Finding duplicate record from table using CTE.

WITH CTE
AS
(SELECT NAME,EMP_ID,ROW_NUMBER()
OVER(PARTITION BY NAME ORDER BY NAME)
AS NAMEDUP FROM EMPLOYEE)
SELECT * FROM CTE WHERE NAMEDUP > 1 ORDER BY NAME

Using Having Count

SELECT NAME,COUNT(NAME)FROM EMPLOYEE
GROUP BY NAME HAVING COUNT(*)>1

Using group by

SELECT NAME,COUNT(NAME) AS CNT
FROM EMPLOYEE
GROUP BY NAME

Deleting duplicate records from the table.

WITH CTE
AS
(SELECT NAME,EMP_ID,ROW_NUMBER()
OVER(PARTITION BY NAME ORDER BY NAME)
AS NAMEDUP FROM EMPLOYEE)
DELETE FROM CTE WHERE NAMEDUP > 1



No comments:

Amazon.in


Don't Copy

Protected by Copyscape Online Plagiarism Checker

Pages

Offers