Eliminating Duplicate Rows In SQL SERVER 2000

jamesd0142 · #1 October 10th, 2007, 04:09 PM

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

Expand|Select|Wrap|Line Numbers

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

jeenajos · #2 November 3rd, 2007, 07:37 AM

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

Regards,
Jeen

qhjghz · #3 November 3rd, 2007, 11:50 AM

Quote:

Originally Posted by jeenajos

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 · #4 November 9th, 2007, 12:59 PM

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

gfajardo · #5 November 16th, 2007, 03:48 PM

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:

Expand|Select|Wrap|Line Numbers

 
select distinct * into temp_table from <original_table>

drop <OrigionalTable>

sp_rename temp_table, original_table

pinaldave · #6 December 25th, 2007, 08:39 AM

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

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

Regards,

Eliminating Duplicate Rows In SQL SERVER 2000

re: Eliminating Duplicate Rows In SQL SERVER 2000

re: Eliminating Duplicate Rows In SQL SERVER 2000

re: Eliminating Duplicate Rows In SQL SERVER 2000

re: Eliminating Duplicate Rows In SQL SERVER 2000

re: Eliminating Duplicate Rows In SQL SERVER 2000

/bytes/development

/bytes/it

/bytes/about