Cleanup duplicated records in a table? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Cleanup duplicated records in a table?

Author

Topic

mapperkids
Yak Posting Veteran

50 Posts

Posted - 2007-01-26 : 06:36:50

Hi,

I would like to cleanup / delete all the duplicated records in a table, could someone tell me how to do it. There are about 500,000 records in it.

Any command / statement that I can use to do it?

Thanks!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-26 : 06:39:20

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=6256

KH

mapperkids
Yak Posting Veteran

50 Posts

Posted - 2007-01-26 : 06:53:29

Thanks Khtan,

But my table don't have primary key, most of the example need a primary key.

Also, I don't how many times the record is being duplicated. some is duplicated 3 times, some duplicated 10 times.

Any idea?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-26 : 08:02:32

you can create a column for the purpose and remove it afterwards

alter table yourtable add [id] int identity(1,1)

...

alter table yourtable drop column [id]

KH

PurpleSun
Yak Posting Veteran

50 Posts

Posted - 2007-01-26 : 13:58:46

Hi mapperkids,

One of the possible solutions to your problem. It's a little clumsy, but it works.
create proc xxx_DeleteDups
as

begin
--declare temp table with all fields from the table that have duplicates
create table #temp1 (id int, fname varchar(50), lname varchar(50), ssn varchar(50))
declare @Id int,
@Again bit;
set @Again = 1
while @Again = 1
begin
insert into #temp1 select top 1 * from xxx group by [id], fname, lname, ssn having count(id)>1
select @ID = (select id from #temp1)
if @ID > 0
begin
delete from xxx where id=@ID
insert into xxx select * from #temp1
delete from #temp1
continue
end
else
begin
set @Again = 0
break
end
end
drop table #temp1

end
go

Good luck

quote:
Originally posted by mapperkids

Hi,

I would like to cleanup / delete all the duplicated records in a table, could someone tell me how to do it. There are about 500,000 records in it.

Any command / statement that I can use to do it?

Thanks!

PurpleSun
Yak Posting Veteran

50 Posts

Posted - 2007-01-26 : 13:59:08

Hi mapperkids,

One of the possible solutions to your problem. It's a little clumsy, but it works.
create proc xxx_DeleteDups
as

begin
--declare temp table with all fields from the table that have duplicates
create table #temp1 (id int, fname varchar(50), lname varchar(50), ssn varchar(50))
declare @Id int,
@Again bit;
set @Again = 1
while @Again = 1
begin
insert into #temp1 select top 1 * from xxx group by [id], fname, lname, ssn having count(id)>1
select @ID = (select id from #temp1)
if @ID > 0
begin
delete from xxx where id=@ID
insert into xxx select * from #temp1
delete from #temp1
continue
end
else
begin
set @Again = 0
break
end
end
drop table #temp1

end
go

Good luck

quote:
Originally posted by mapperkids

Hi,

I would like to cleanup / delete all the duplicated records in a table, could someone tell me how to do it. There are about 500,000 records in it.

Any command / statement that I can use to do it?

Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources