List:General Discussion« Previous MessageNext Message »
From:Brent Baisley Date:November 21 2008 7:21pm
Subject:Re: SELECT through many databases
View as plain text  
A MERGE table is just a virtual table that is made up of other tables.
You treat it no differently than any other table, pretend it's a real
table.

You could even create multiple MERGE tables from different tables. A
good example is Q1, Q2, Q3, Q4, LatestQ, for quarterly information.
You just roll the underlying tables from one to the other as quarters
close, you never have to change your scripts or move data around.
Underlying tables can be members of more than one MERGE table.

You can even INSERT into them, just specify which underlying table the
new data is supposed to go into.

On Fri, Nov 21, 2008 at 2:12 PM, Andre Matos <andrematos@stripped> wrote:
> Sounds interesting, but does the MERGER support complex SELECT statements
> and LEFT JOIN?
>
> Andre
>
>
> On 21-Nov-08, at 1:45 PM, Brent Baisley wrote:
>
>> On Fri, Nov 21, 2008 at 12:44 PM, Andre Matos <andrematos@stripped>
>> wrote:
>>>
>>> Hello,
>>>
>>> Let's suppose I have 5 database: db1, db2, db3, db4, and db5. They all
>>> have
>>> the same structure but different data.
>>>
>>> I would like perform this select
>>>
>>> SELECT TaskDoneOn, TaskDoneBy
>>> FROM {database}
>>> WHERE TaskDoneOn IS NOT NULL
>>>
>>> and collect the data from all 5 database. However, I would like to avoid
>>> doing something like this:
>>>
>>> SELECT TaskDoneOn, TaskDoneBy
>>> FROM db1
>>> WHERE TaskDoneOn IS NOT NULL
>>> UNION
>>> SELECT TaskDoneOn, TaskDoneBy
>>> FROM db2
>>> WHERE TaskDoneOn IS NOT NULL
>>> UNION
>>> SELECT TaskDoneOn, TaskDoneBy
>>> FROM db3
>>> WHERE TaskDoneOn IS NOT NULL
>>> UNION
>>> SELECT TaskDoneOn, TaskDoneBy
>>> FROM db4
>>> WHERE TaskDoneOn IS NOT NULL
>>> UNION
>>> SELECT TaskDoneOn, TaskDoneBy
>>> FROM db5
>>> WHERE TaskDoneOn IS NOT NULL
>>>
>>>
>>> Today I have 5, but tomorrow I can have 50 and I don't want to forget any
>>> database.
>>>
>>> Thanks for any help.
>>>
>>> Andre
>>
>> Create a MERGE table that is all those tables combined. Then you just
>> need to do 1 select as if it was one table. Just be sure to update the
>> MERGE table description when ever you add a table.
>>
>> Brent Baisley
>
> --
> Dr. André Matos
> andrematos@stripped
>
>
>
>
Thread
SELECT through many databasesAndre Matos21 Nov
  • Re: SELECT through many databasesPeter Brawley21 Nov
    • Re: SELECT through many databasesAndre Matos21 Nov
      • RE: SELECT through many databasesJerry Schwartz21 Nov
        • Re: SELECT through many databasesAndre Matos21 Nov
          • Re: SELECT through many databasesOlexandr Melnyk21 Nov
            • Re: SELECT through many databasesAndre Matos21 Nov
  • Re: SELECT through many databasesBrent Baisley21 Nov
    • Re: SELECT through many databasesAndre Matos21 Nov
      • Re: SELECT through many databasesBrent Baisley21 Nov
        • RE: SELECT through many databasesMartin Gainty21 Nov
  • Re: SELECT through many databasesClaudio Nanni21 Nov
Re: SELECT through many databasesOlexandr Melnyk21 Nov