Eliminating Duplicate Rows In SQL SERVER 2000

My manager and I where looking at some complex code to eliminate duplicate records in a database table.

then it hit me how its done easily... so i thought i'd share it...

In English:
--------------------------------
-- Eliminating Duplicate rows --
--------------------------------
-- select all into a temp table.
-- truncate origional table.
-- select only unique rows back into origional table.

In Code sql server 2000:
--------------------------------
-- Eliminating Duplicate rows --
--------------------------------

re: 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

re: Eliminating Duplicate Rows In SQL SERVER 2000

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

re: Eliminating Duplicate Rows In SQL SERVER 2000

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

re: Eliminating Duplicate Rows In SQL SERVER 2000

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:

re: Eliminating Duplicate Rows In 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,