Code Project

Link Unit

Wednesday, July 11, 2007

Delete Duplicate Rows

You may like to try these methods.
1. Add an idenity Column to the table
Alter table yourtable Add myseq int identity(1,1)

2. Delete all the rows where rows are duplicate based on the condition
Delete from yourTable where Exists ( Select 1 from YourTable B where Yourtable.column1=B.column1 and Yourtable.column2=B.column2 and Yourtable.myseq < B.myseq )

Or simple
1. Select distinct * into newTable from YourTable
2. delete from Yourtable
truncate table Yourtable
3. insert into Yourtable select * from newTable