Remove Duplicate Rows from a Table in SQL Server
|
Jak
858
Points
132
Posts
|
Hi, I have a table that have duplicate row. How can I remove dulicate.
Views:
9018
Total Answered: 2 Total Marked As Answer: 1 Posted On: 31-Oct-2014 23:05 Share: 
|
Answers
|
Nice One
280
Points
0
Posts
|
Hi, Suppose you have Emp_NICE Table with duplicate rows --Insert distinct data to temp table #tmp_NICESELECT DISTINCT * INTO #tmp_NICE FROM Emp_NICE--Delete all data from Emp_NICEDELETE FROM Emp_NICE--Inert Distinct data from #tmp_NICE to Emp_NICEINSERT INTO Emp_NICE SELECT * FROM #tmp_NICE --Drop temp table #tmp_NICEDROP TABLE #tmp_NICE--See distinct recordSELECT * FROM Emp_NICE
Posted On:
31-Oct-2014 23:47
|
Mayank
194
Points
12
Posts
|
You can use ROW_NUMBER() with PARTITION BY to delete duplicate. Use following CTE Query to delete duplicate record from the table tblCustomer: WITH tmp AS(SELECT Customer_id,Customer_name,Customer_gender,Customer_age,ROW_NUMBER() OVER(PARTITION BY Customer_id,Customer_name,Customer_gender,Customer_age ORDER BY name) AS duplicateCount FROM tblCustomer)
DELETE FROM tmp WHERE tmp.duplicateCount>1
Posted On:
18-Feb-2016 02:18
|
