Does anyone know how to view and delete identical items on table?
What is SQL command to do this. Thanks

Here are a couple of resources that may guide you.

http://www.aspfaq.com/2431
http://www.sql-server-performance.com/rd_delete_duplicates.asp

Show quoteHide quote

Why do you have redundant duplicates at all?  The PRIMARY KEY should
have prevented the problem.

If we show you how to clean up your mess, then will you promise to add
a real key to the table?  Mop the floor then fix the damn leak!

Perhaps they are importing data from multiple systems.

Perhaps they are deduplicating address information.

sh*t man - you really ought to get out of the class room and get a job and
some experience....

Show quoteHide quote

Qnguyen wrote:
set nocount on
declare @id int,@rc int
create table #WithDup (id int)
insert into #WithDup(id) select id from sysobjects
insert into #WithDup(id) select id from sysobjects

select top 10 id,count(*) from #withDup group by id having(count(*))>1
order by 2 desc

declare cur cursor local static forward_only for
select id,count(*)-1 from #WithDup group by id having(count(*))>1

open cur
fetch next from cur into @id,@rc
while @@fetch_status = 0 begin
    set rowcount @rc
    delete #WithDup where id = @id
    set rowcount 0
fetch next from cur into @id,@rc
end
deallocate cur

select top 100 id,count(*) from #withDup group by id having(count(*))>1
order by 2 desc

drop table #withdup

Hi There,

You may like to try these methods.

1. Add an idenity Column to the table
Allter 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
3. insert into Yourtable select * from newTable

I hope this helps

With Warm regards
Jatinder Singh
http://jatindersingh.blogspot.com
http://sqloracle.tripod.com

or
1. create #NewTable
2. create unique index Name_index on #NewTable(column or columns)
3 insert #NewTable select YourTable with IGNORE_DUP_KEY
4 delete from YourTable
5 insert into Yourtable select * from #NewTable

but I preffer jsfromynr solution...

Returning selected rows
Create view of winning lottery numbers
Performance issues using Parameters
Dynamic SQL Problem..
Timeout - max pool size reached