November 24, 2004 at 3:02 pm
Hey
I just got this error when I was trying to look at one of mine tables.
The table contains more than 1 000 000 posts, this is not correct and I expect this is becuase I have some duplicate keys in the table.
Wwhat is the most effective way to find duplicate keys?
regards
DJ
November 24, 2004 at 3:22 pm
From the MS Access find duplicates query wizard...
SELECT First(dbo.tablename.key_column) AS [DUPE_ID], Count(dbo.tablename.key_column) AS NumberOfDups
FROM dbo.table
GROUP BY dbo.tablename.key_column
HAVING (((Count(dbo.tablename.key_column))>1));
Sean Wyatt
seanwyatt.com
November 25, 2004 at 12:40 am
Thanks for your tip.
Do you know if this will work whwn you have more than one key.
I have two key`s.
Will try your tip and hope that helps to find the duplicate.
November 25, 2004 at 1:36 am
In case it doesn't help do a search here in the script section.
Identifying duplicates is one of the most frequently asked questions. So it's very likely you'll find a solution there.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
November 27, 2004 at 7:24 am
Do you want to automatically delete the dupes (leaving only the earliest or the latest) or just find them? Frank is correct... the search leads to lot's of "duplicate row" goodies but I've got a pretty nifty way of doing it without cursors or WHILEs. If you post the two fields you want to key on, a date field (ID field will also be OK to select the earliest or latest) and an IDENTITY field of some sort, I'll get back to you with a script.
Also, you say there's more than a million records and that that's not correct... any idea on approximately how many there should be (for my testing/timing)?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply