Data Engineering Technical Blog

Delete duplicates from table in SQL There are different ways to delete duplicates from a table, we often get duplicates while loading data in tables which does not contain any primary key. Lets say I have below table and we need to delete duplicates from the table. EmpName Ram Gopi Ram Radha Delete duplicates with help of temp table We first try to load the distinct records in a temporary table and then delete all records from main table and the reload the main table from temporary table. SELECT DISTINCT EmpName INTO #Data FROM Employee DELETE FROM Employee INSERT INTO Employee (EmpName) SELECT EmpName FROM #Data Delete duplicate using CTE We use row_number() function and common table expression to delete duplicate. ;with cte as ( SELECT *,ROW_NUMBER() OVER (PARTITION BY EmpName ORDER BY EmpName) AS RNK FROM Employee ) DELETE FROM cte WHERE RNK > 1