Hi ,

I have tried duplicate record deletion without using temp tables.

And according to me its faster and a very short query.

CREATE TABLE EMP

(EMPID INT,EMPNAME VARCHAR(100))

INSERT INTO EMP VALUES (1,'A')

INSERT INTO EMP VALUES (1,'A')

INSERT INTO EMP VALUES (1,'A')

INSERT INTO EMP VALUES (2,'B')

INSERT INTO EMP VALUES (2,'B')

INSERT INTO EMP VALUES (3,'C')

INSERT INTO EMP VALUES (4,'D')

INSERT INTO EMP VALUES (4,'D')

INSERT INTO EMP VALUES (4,'D')

INSERT INTO EMP VALUES (4,'D')

INSERT INTO EMP VALUES (5,'E')

INSERT INTO EMP VALUES (5,'E')

INSERT INTO EMP VALUES (6,'F')

INSERT INTO EMP VALUES (6,'F')

INSERT INTO EMP VALUES (6,'F')

INSERT INTO EMP VALUES (6,'F')

INSERT INTO EMP VALUES (6,'F')

INSERT INTO EMP VALUES (7,'G')

SELECT * FROM EMP

SELECT @@ROWCOUNT

--(Initially the rowcount must be > 0 )

WHILE @@ROWCOUNT > 0

DELETE TOP(1) FROM EMP WHERE EXISTS

(SELECT E.EMPID ,COUNT(E.EMPID) FROM EMP E

WHERE EMP.EMPID=E.EMPID GROUP BY E.EMPID HAVING COUNT(E.EMPID) > 1)

SELECT * FROM EMP

Correct if wrong ........................

:smooooth: