Union Query

[carlos]

I have a union query that shows records of all available
tables. TABLE [tblA] UNION ALL TABLE [tblB] UNION ALL
TABLE [tblC]…etc..

Every time I create a new table I will also modify the SQL
code to include the new table. What sort of code I need so
it will automatically retrieve all records of all
available tables without modifying my SQL statement?

Any advice will be appreciated.

Thanks in advance.

carlos

 

[Douglas J. Steele]

Why are you creating new tables that are identical in structure so that you
need to union them together? Sounds to me as though you're mistakenly
putting details for each customer in a separate table, or something like
that.

Perhaps if you describe what you're trying to do, we'll be able to suggest a
better way.

 

[John Vinson]

Every time I create a new table I will also modify the SQL
code to include the new table. What sort of code I need so
it will automatically retrieve all records of all
available tables without modifying my SQL statement?

You can't. And I'm with Douglas - this seems like a very strange thing
to want to do!

Why not go the other way - put all the data in one table, and use
Queries to break out subsets, rather than having non-normalized
multiple tables as subsets and having to UNION them together? If the
table is properly indexed you'll get much better performance.