Query across multiple databases on same server
- Status: Closed
- Hadiah: $300
- Penyertaan diterima: 8
- Pemenang: sopholos
1. We have a database server with multiple databases on it (all have the same schema, different data)
lets call it company A and company B
2. We are looking for a way to query across all the databases or some databases
3. The end user would select company A,B,AB,AC
4. Needs to be easy to configure, as more databases may be added at any time. This data access must be real-time.
5. We envision having a function written in Delphi that would dynamically restructures the SQL in such a way to enable this functionality
Say, as an example, you have an application that inserts orders - each application has its own DB running as a separate company . What we are then looking for is an efficient way for a single application to then access the order information in all the other databases in order to run reports as one company.
This is an Example of SQL we now use.
Select [login to view URL], [login to view URL], [login to view URL], [login to view URL], [login to view URL], [login to view URL], [login to view URL], Year([login to view URL]) [Year], Month([login to view URL]) [Month], Sum([login to view URL]) Sales
From TicketHD h, Customer c
Where [login to view URL] Between '01/01/2015' And '06/30/2015'
And c.Tel=[login to view URL]
Group By 1, 2, 3, 4, 5, 6, 7, 8, 9;
I would test each solution on this example.
The database server we are using is Advantage database.
[login to view URL]
I believe any generic solution would work.
Maybe fireDac could do the trick somehow
Maklum balas Majikan
“Did a great job would hire again. ”
ProgramDelphi, United States.