Monday, March 10, 2008

how do you delete duplicates rows from a table

1. copy the structure of base_temp into temp_table

select * into temp_table from base_temp where 1=0
2. create a unique index on the columns that contains the duplicate rows with the ignore_dup_key attribute .this may be more columns the key for the table.

Create unique index temp_idx on temp_table(col1,col2…..) with igmore_dup_key
3. Now insert base_table into temp_table
Insert temp_table select * from base_table
Truncate the base table and copy the data in temp_table to base_table

3 comments:

NITESH KAPOOR said...

Replace the keyword igmore_dup_key with ignore_dup_key than it works fine.

Rathod said...

For Delete duplicate row
(Step - 1) SET ROWCOUNT 1 --(row count -1)
(Step - 2) DELETE FROM Tablename and your condition

Narasimha said...

This link provides different methods of deleting the duplicate rows

http://www.besttechtools.com/SQLArticles.aspx?ID=DeleteDuplicate