SQL select information from two databases

[Naim Kamel]

Hi
I'm working on a ColdFusion project that has more than 8 diffrent data sources.
I'm trying to select the information inside a table called Latesnews.
This table has the same structure in all the 8 datasources.
Now, how can i list all of them in one page and sort them according to the
date or something?

[MikerRoo]

If you have a high end data base like SQL server or Oracle, it is more
efficient to do this kind of thing using their tools (if you can).

The attached code shows the gist of doing it all in Coldfusion.

If the tables really do have identical structures and names, you can just loop
though the same query 8 times, changing the query name and data source at each
pass.
I showed duplicate code, just in case.



<CFQUERY name="qNewsSource1" datasource="DATA_SOURCE_1">
SELECT
StoryHeadline
, StoryText
, StoryDate
FROM
Latesnews
WHERE
...Whatever ...
</CFQUERY>

<CFQUERY name="qNewsSource2" datasource="DATA_SOURCE_2">
SELECT
StoryHeadline
, StoryText
, StoryDate
FROM
Latesnews
WHERE
...Whatever ...
</CFQUERY>


... ETC, ETC, ...


<CFQUERY name="qNewsSource8" datasource="DATA_SOURCE_8">
SELECT
StoryHeadline
, StoryText
, StoryDate
FROM
Latesnews
WHERE
...Whatever ...
</CFQUERY>


<!--- Now put all the datasources together. If their are duplicates, change
the "union all"
Statements to just "union" -- but this can be quite a bit slower.
--->

<!--- Use a query of queries --->

<CFQUERY name="qALL_NewsStories" dbtype="query"> <!--- Note, no datasource
--->

SELECT StoryHeadline, StoryText, StoryDate FROM qNewsSource1
UNION ALL
SELECT StoryHeadline, StoryText, StoryDate FROM qNewsSource2
UNION ALL
SELECT StoryHeadline, StoryText, StoryDate FROM qNewsSource3
UNION ALL
SELECT StoryHeadline, StoryText, StoryDate FROM qNewsSource4
UNION ALL
SELECT StoryHeadline, StoryText, StoryDate FROM qNewsSource5
UNION ALL
SELECT StoryHeadline, StoryText, StoryDate FROM qNewsSource6
UNION ALL
SELECT StoryHeadline, StoryText, StoryDate FROM qNewsSource7
UNION ALL
SELECT StoryHeadline, StoryText, StoryDate FROM qNewsSource8

ORDER BY
StoryDate DESC,
StoryHeadline
</CFQUERY>

[Naim Kamel]

nice idea.
Since i'm using Access here is what another friend told me.
In access they have the link table thing. so i liked all the tables into the
main database and used the union thing.
Both ways works but i have to test which is quicker.

[Naim Kamel]

nice idea.
Since i'm using Access here is what another friend told me.
In access they have the link table thing. so i liked all the tables into the
main database and used the union thing.
Both ways works but i have to test which is quicker.

[Naim Kamel]

nice idea.
Since i'm using Access here is what another friend told me.
In access they have the link table thing. so i liked all the tables into the
main database and used the union thing.
Both ways works but i have to test which is quicker.