6

I have studied on it and the most practical way I found was to create a procedure. The procedure
adds an id, delete duplicate rows, and finally adds an id column to newly formed table. I wonder, there must be an easier way. Here's the code, it works...

--my table

create table dublicateTable
(
name varchar(30)
)

--duplicatedly inserted 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

--dynamicaly formed sql code procedure

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
My-Name-Is
  • 4,814
  • 10
  • 44
  • 84
kadir bozan
  • 71
  • 1
  • 6

1 Answers1

3

A CTE can make it much simpler 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
JC Ford
  • 6,946
  • 3
  • 25
  • 34
  • It's not necessary to include the 'name' field in the cte, since it's not used directly in the query. – Anon Jul 02 '13 at 20:45