Eliminating Duplicate Rows In SQL SERVER 2000

[jamesd0142]

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 --
--------------------------------
[code=sql]
-- select all into a temp table.

select * into temptable1 from <OrigionalTable >
-- truncate origional table.
delete <OrigionalTable >

-- select only unique rows back inot origional table.
--this usues the selected columns to see if its unique
insert into <OrigionalTable > select Distinct <columnName> from TempTable1

--this uses all columnsto see if its unique
insert into <OrigionalTable > select Distinct * from TempTable1
[/code]

[jeenajos]

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

Regards,
Jeen

[qhjghz]

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)

[Erenst]

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_curso r 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_curso r

FETCH NEXT FROM duplicate_curso r
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_curso r
INTO @Column1, @Column2, ect ...
END

CLOSE duplicate_curso r
DEALLOCATE duplicate_curso r

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

[gfajardo]

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:

[code=sql]
select distinct * into temp_table from <original_table >
drop <OrigionalTable >
sp_rename temp_table, original_table
[/code]

[pinaldave]

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

http://blog.sqlauthority.com/2007/03/01/sql-server-delete-duplicate-records-rows/

Regards,