Eliminating Duplicate Rows In SQL SERVER 2000

But wen im having multiple column duplication the above code doesnt works. In that situation wat can i do???

Regards,
Jeen

But wen im having multiple column duplication the above code doesnt works. In that situation wat can i do???

Regards,
Jeen

DELETE FROM table_name where column_name in
(select column_name from table_name GROUP BY column_name HAVING COUNT(*)>1)

This is what i've done .. creating an cursor and I've used it many times, hope it can help you.


--Declare all column names in table

DECLARE @Column1 decimal(9), @Column2 varchar(50), ect .....

DECLARE duplicate_cursor CURSOR FOR

-- select all columns in table bit you must have an count column
select Column1,Column2, count(*) Cnt, ect ...
from tblTable
group by Column1, Column2
Having count(*) > 1

OPEN duplicate_cursor

FETCH NEXT FROM duplicate_cursor
INTO @Column1, @Column2, ect ...

WHILE @@FETCH_STATUS = 0
BEGIN

SET @Cnt = @Cnt - 1

SET ROWCOUNT @Cnt

DELETE tblTable
WHERE @Column1 = Column1 AND @Column2 = Column1 AND ect ....


FETCH NEXT FROM duplicate_cursor
INTO @Column1, @Column2, ect ...
END

CLOSE duplicate_cursor
DEALLOCATE duplicate_cursor

-- dont forget to set rowcount to 0
SET ROWCOUNT 0

Most easy and fast.
Aply only for duplicate rows in all columns.
be carefully with the indexes and foreign key before to drop the original table.

In Code sql server 2000:

Following website demonstrates easiest way to delete duplicate records from SQL Server (any version).

http://blog.sqlauthority.com/2007/03...-records-rows/

Regards,