November 29, 2007 at 3:44 am
Can someone help in building a query through which i can determine if a table has duplicate rows and what are the duplicate rows?
November 29, 2007 at 3:54 am
Just google on "sql duplicate records rows" and you will probably find hundreds of these scripts.
[font="Verdana"]Markus Bohse[/font]
November 29, 2007 at 6:45 am
Or do a search in the little box near the top of the page. You'll find tons of discussions and articles and scripts on the topic, all on this site.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
November 29, 2007 at 8:46 am
I'll make it easy for you 🙂
-- Identify Duplicate Rows
select SomeColumn,SomeKey
from MyTable
group by SomeColumn,SomeKey
having count(*) > 1;
-- Nuke Duplicate Rows
alter table MyTable
add RowID int identity(1,1)
delete from MyTable
where RowID NOT IN
(select min(RowID) from MyTable
group by SomeColumn,SomeKey)
alter table MyTable
drop column RowID
go
-- Verify
select SomeColumn,SomeKey
from MyTable
group by SomeColumn,SomeKey
having count(*) > 1;
Tommy
Follow @sqlscribeViewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply