Remove duplicate rows from table

By mistakenly , non-primary key table data inserted into SQL Table. what you will do ?

We dun have any option without deleting it. If we not used Savepoint , then how to rollback inserted data ?

Remove table , --- > No

Remove rows one by one --> No

Use CTE ( Common Table Expression)

Query :

I have inserted following data two times

insert into [Test].[dbo].[Student] values(1,'nihar','pune')
insert into [Test].[dbo].[Student] values(2,'rohan','nashik')
insert into [Test].[dbo].[Student] values(3,'vishal','pabal')


Remove duplicate rows :

Query :

 
 WITH OrderedResults AS
(
SELECT [studno]
, ROW_NUMBER() OVER (PARTITION BY [studno] ORDER BY [studno]) AS RowNumber
FROM [Test].[dbo].[Student]

)delete
from OrderedResults
WHERE RowNumber!= 1   Select whole query and execute it, Duplicate entries will be removed from table.Think over it.