No more selecting DISTINCT into a temp table and then inserting back into the cleaned out table. This does it all for you in two statements.
Deleting from the CTE actually changes the underlying table. Be careful how you setup your CTE. You could have some unintended deletes without the right logic.
CREATE TABLE #prod(
Product_Code varchar(10),
Product_Name varchar(100)
)
INSERT INTO #prod(Product_Code, Product_Name)
VALUES ('123','Product_1')
INSERT INTO #prod(Product_Code, Product_Name)
VALUES ('234','Product_2')
INSERT INTO #prod(Product_Code, Product_Name)
VALUES ('345','Product_3')
INSERT INTO #prod(Product_Code, Product_Name)
VALUES ('345','Product_3')
INSERT INTO #prod(Product_Code, Product_Name)
VALUES ('456','Product_4')
INSERT INTO #prod(Product_Code, Product_Name)
VALUES ('567','Product_5')
INSERT INTO #prod(Product_Code, Product_Name)
VALUES ('678','Product_6')
INSERT INTO #prod(Product_Code, Product_Name)
VALUES ('789','Product_7')
SELECT *
FROM #prod;
With Dups as
(
select *, row_number() over (partition by Product_Code order by Product_Code) as RowNum
from #prod
)
Delete from Dups where rownum > 1;
--Note duplicate record 345 Product_3 has been removed.
SELECT *
FROM #prod; 
2014-08-28
2,170 reads
2014-06-17
2,093 reads
In finance, when pricing data isn't available on last day of week, month or year, calculating returns is tricky. This article addresses the issue.
2011-10-18
8,616 reads
A discussion on using CTEs to speed the development and maintenance of reports and enhance readability.
2008-03-31
6,080 reads
They are new to SQL Server in 2005, but they are not necessarily in common use for many people. New author Kirk Kuykendall brings us a basic look on what a CTE is and how you can use it.
2008-03-18
14,393 reads