|
|
Posted by Dan Guzman on 02/15/06 03:53
Better too much than too little :-)
--
Hope this helps.
Dan Guzman
SQL Server MVP
"AK" <ambkh@yahoo.com> wrote in message
news:1139936166.210532.262820@g43g2000cwa.googlegroups.com...
> 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
>> >
>
Navigation:
[Reply to this message]
|