How to remove duplicate (duplicate) records, rows from a table without identifier

Question

I studied it, and the most practical way I found was to create a procedure. The procedure adds an identifier, removes duplicate rows, and finally adds an identifier column to the newly formed table. Interestingly, there should be an easier way. Here is the code, it works ...

- my table

create table dublicateTable ( name varchar(30) ) 

- duplicate rows

 insert into dublicateTable values('Kerem') insert into dublicateTable values('Taner') insert into dublicateTable values('Mehmet') insert into dublicateTable values('Serhat') 

- first situation

 select * from dublicateTable name ----- Kerem Kerem Kerem Taner Taner Mehmet Mehmet Mehmet Mehmet Serhat Serhat Serhat 

- dynamic procedure for generating sql code

 USE [myDataBase] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO create procedure [dbo].[usp_delete_duplicate] as declare @add_id_text nvarchar(50), @delete_id_text nvarchar(50), @command_text nvarchar(100) begin set @add_id_text='alter table dbo.dublicateTable add id int identity(1,1)' set @command_text='delete from dbo.dublicateTable where id not in(select min(id) from dbo.dublicateTable group by name)' set @delete_id_text='alter table dbo.dublicateTable drop column id' exec sp_executesql @add_id_text exec sp_executesql @command_text exec sp_executesql @delete_id_text exec sp_executesql @add_id_text end 

- final situation .it works.

 exec usp_delete_duplicate select id,name from dublicateTable id name --- ---- 1 Kerem 2 Taner 3 Mehmet 4 Serhat 

Answer 1

CTE can make it a lot easier to write.

 ;with cte as ( select name, row_number() over (partition by name order by name) row from dublicateTable ) delete from cte where row > 1