List:General Discussion« Previous MessageNext Message »
From:Brent Baisley Date:November 21 2008 6:45pm
Subject:Re: SELECT through many databases
View as plain text  
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
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