By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,319 Members | 2,361 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,319 IT Pros & Developers. It's quick & easy.

Eliminating Duplicate Rows In SQL SERVER 2000

jamesd0142
100+
P: 469
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
  1. -- select all into a temp table.
  2.  
  3. select * into temptable1 from <OrigionalTable>
  4. -- truncate origional table.
  5. delete <OrigionalTable>
  6.  
  7. -- select only unique rows back inot origional table.
  8. --this usues the selected columns to see if its unique
  9. insert into <OrigionalTable> select Distinct <columnName>  from TempTable1
  10.  
  11. --this uses all columnsto see if its unique
  12. insert into <OrigionalTable> select Distinct * from TempTable1
  13.  
Oct 10 '07 #1
Share this Question
Share on Google+
5 Replies


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

Regards,
Jeen
Nov 3 '07 #2

P: 26
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)
Nov 3 '07 #3

P: 2
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
Nov 9 '07 #4

P: 4
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
  1. select distinct * into temp_table from <original_table>
  2. drop <OrigionalTable>
  3. sp_rename temp_table, original_table
  4.  
Nov 16 '07 #5

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

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

Regards,
Dec 25 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.