delete the duplicate

[sandeeppradhan]

How to delete the duplicate row from a table without using cursor in microsoft sql server
If any one know then please send me the answer
Thank you
sandeep kumar pradhan

[jagadishbabub]

Hi Sandeep,

Trying out for a best solution but is giving some error...........
Meanwhile if it is very urgent ........ you can use this .........
ofcourse this is not recommended ..........

CREATE PROCEDURE dbo.PROC_Eliminate_Duplicate_Rows
AS
SET NOCOUNT ON
CREATE TABLE #TBL_Duplicate (EMPNo int, EMPName varchar(50))
INSERT INTO #TBL_Duplicate SELECT DISTINCT * FROM EMP_Duplicate
DELETE EMP_Duplicate
INSERT INTO EMP_Duplicate SELECT * FROM #TBL_Duplicate
SELECT * FROM EMP_Duplicate
RETURN



Regards,
Jagadish Babu

[srichandr]

Hai Sandeep,

Please try

SET ROWCOUNT 1
DELETE temp_test
FROM temp_test a
WHERE (SELECT COUNT(*) FROM temp_test b WHERE b.name = a.name and b.value = a.value) > 1
WHILE @@rowcount > 0
DELETE temp_test
FROM temp_test a
WHERE (SELECT COUNT(*) FROM temp_test b WHERE b.name = a.name and b.value = a.value) > 1
SET ROWCOUNT 0


Regards,
Srichand