deleting dubilicate rows from tables

Dear user !

we want to delete duplicate rows from tables in sql server 2005. please guide us how can do this work.

 

If your table does not have PRIMARY ID you have to use temp table:
- CREATE temp_table
- INSERT INTO temp_table SELECT DISTINCT * FROM table
- TRUNCATE TABLE table
- INSERT INTO table SELECT * FROM temp_table.

If you have primary id for each row, the it is a little bit tricky. If you have another tables that point to the table, things might get messy

 

DELETE FROM our_table
WHERE rowid not in
(SELECT MIN(rowid)
FROM our_table
GROUP BY column1, column2, column3... ;

Here column1, column2, column3 constitute the identifying key for each record.

 

Thanks Mr. Carol Smith ! We are very grateful for your quick response .