Remove duplicate rows from a table
-
Hi guys
I have been using SQL server 2005. I have got a huge table with about 1 million rows.
Problem is this table has got duplicate rows in lot of places. I need to remove the these duplicates. Is there an easy way to do that??
Is there a query in SQL to remove duplicate rows???
Thanks
Mita
-
Select distinct * INTO #tmp from YourTable
Truncate Table YourTable
Insert YourTable Select * from #tmp
-
Ronald's suggestion will certainly work but it's a pain if you have a bunch of named indexes, constraints, and the like...
If you'd take the time to post the schema (CREATE STATEMENT PREFERRED!) and identify the columns that you'd like to check for dupes, I've got a nasty fast little routine that will easily handle a million rows. I'd also need to know the column or columns that you use for a Primary Key and whether it it's a clustered key or not. And, if it's not clustered, I need to know the columns for the clustered index if you have one.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
No response... guess it's no longer a priority, eh?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply