Union

[azzazzello]

I have X number of identical (structure-wise) tables, with one generated for every day. There is a reporting tool which requires that certain data be extracted for a given time period, which can span any number of these databases. My idea is to use a dynamically generated UNION ALL statement which simply joins identical selects for each table. e.g.

SELECT id, blah, bleh FROM t1
UNION ALL
SELECT id, blah, bleh FROM t2
....

Here is a problem...Suppose I have a SELECT * statement that I need to do this to, and my table structure changes (which is plausibly can). Then a query using "SELECT *" is run, and it spans the 2 different-structure databases. The statement will fail, which is bad. Is there something I can do other than iterating through each table in my code? (which I am reluctant to do)

 

[TonyGroves]

Do you mean that you would like to do:[tt]
SELECT * FROM t1
UNION ALL
SELECT * FROM t2
UNION ALL
...[/tt]
?

If it is possible that not all the tables will have identical structures, then that is a very unreliable query, as it will fail only if the number of columns is different. If it does work, it will not even make any effort to check the field names or data types in each table; it will put all the first-column values of each table into the first column of the result set, and so on.

If you want a unioned query to automatically extract all the fields that the tables have in common, then you you would need to use a program to get the field names for each table and assemble a suitable query in which the fields are named.