Posted by AK on 02/14/06 18:56

Thank you Dan. This is exactly what I needed (in fact more than what I
needed :)

Regards
AK

Dan Guzman wrote:
> > Here is my question: How can I find how many unique or duplicate
> > entries they have across all the 10 databases.
>
> The following will list the count of unique values (Duplicates = 0) as well
> has the non-unique values grouped by the number of duplicates (1-9).
>
> SELECT
> Duplicates,
> (Duplicates + 1) * COUNT(*) AS TotalHashIDCount
> FROM (
> SELECT HashID, COUNT(*) - 1 AS Duplicates
> FROM (
> SELECT HashID FROM db1.dbo.Message
> UNION ALL SELECT HashID FROM db2.dbo.Message
> UNION ALL SELECT HashID FROM db3.dbo.Message
> UNION ALL SELECT HashID FROM db4.dbo.Message
> UNION ALL SELECT HashID FROM db5.dbo.Message
> UNION ALL SELECT HashID FROM db6.dbo.Message
> UNION ALL SELECT HashID FROM db7.dbo.Message
> UNION ALL SELECT HashID FROM db8.dbo.Message
> UNION ALL SELECT HashID FROM db9.dbo.Message
> UNION ALL SELECT HashID FROM db10.dbo.Message
> ) AS Messages
> GROUP BY HashID) AS HashIDCounts
> GROUP BY Duplicates
> ORDER BY Duplicates
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
>
> "AK" <ambkh@yahoo.com> wrote in message
> news:1139679057.443925.49210@g44g2000cwa.googlegroups.com...
> > Hi
> >
> > Our product uses MS-SQL Server 2000. One of our customer has 10
> > installations with each installation stroring data in its own database.
> > Now the customer wants to consolidate these databases into one and we
> > already have plan for that by consolidating one DB at a time. But first
> > they want to find how many unique or duplicate entries they have across
> > all the 10 databases
> >
> > Assumptions:
> > 1. All the databases reside on the same server. (This is just an
> > assumption, not the real environment at customer site)
> > 2. Databases can not be merged before it is found how many unique or
> > duplicate rows exist.
> >
> > Table under consideration:
> > Message
> > (
> > HashID PK,
> > ...
> > )
> >
> > # of rows in Message table in each of databases: 1 Million
> >
> > Here is my question: How can I find how many unique or duplicate
> > entries they have across all the 10 databases. I easily find unique
> > rows for two databases with a query like this:
> >
> > SELECT COUNT(A.HasID) FROM db1.dbo.Message A LEFT OUTER JOIN ON
> > db2.dbo.Message B ON A.HashID = B.HashID WHERE B.HashID IS NULL
> >
> > How can I do this for 10 databases. This will require factorial of 10
> > queries to solve this problem.
> >
> > I will appreciate if someone can provide hint on this.
> >
> > Regards
> > AK
> >

[Back to original message]