Find / Remove Duplicate From Table T-SQL Query





Find Duplicate Record Query




Select
       MemberID,
       Name,
       Count(MemberID) AS NumOccurrences
From Member
Group By
        MemberID,
       Name
Having (Count(MemberID) > 1)




Result







Remove Duplicate Query





;With MemberCte as
(
Select    *,
             ROW_NUMBER()OVER
             (
             PARTITION BY
                     MemberID,
                     Name
ORDER BY
              Last_Upd_Date
              )
AS RowNumber


FROM Member
 )
Delete From MemberCte


WHERE RowNumber > 1



Result










If  You Want to Remove All Duplicate Row



DELETE   FROM   Member
    WHERE MemberID IN
(
Select MemberID
      FROM Member
GROUP BY
       MemberID
Having Count(*) >1
)

Result


Delete Row For Particular MemberID


Delete  Top(2) From Member Where MemberID = 1

Result



Enter into another table of Duplicate before Removing Data


;With MemberCte as
(
Select    *,
             ROW_NUMBER()OVER
             (
             PARTITION BY
                     MemberID,
                     Name
ORDER BY
              Last_Upd_Date
              )
AS RowNumber


FROM Member
 )

Select *
Into #TempMember -- Table to add Duplicate Records

From MemberCte

WHERE RowNumber > 1
Labels: