Posted by Steve on 11/30/05 02:26

This is a "commercial" database solution purchased to record and report
environmental data. I don't like the design for all the reasons already
posted, and more. Though I had nothing to do with the selection,
unfortunately, I am charged with completing a task, so like it or not, it
is on my plate.
Terry Kreft's idea looks workable, but not optimum. It would seem like there
would be a way to get around pasting strings together and then executing
the string. I was wondering if there was a way to use a subquery to return
a table reference. It is a rather obtuse way of getting there, and I
haven't investigated it yet. Any alternate solutions anyone can think of?

Thanks for the responses,
Steve


David Portas wrote:

> Steve wrote:
>> I am trying to develop a stored procedure for an existing application
>> that has data stored in numerous tables, each with the same set of
>> columns. The main columns are Time and Value. There are literally
>> hundreds of these tables that are storing values at one minute intervals.
>> I need to calculate the value at the end of the current hour for any
>> table. I am a little new to SQL Server, but I have some experience with
>> other RDBMS.
>>
>
> Just to emphasise what Steve Jorgensen said: this sounds like a crazy
> design. If you are forced to support hundreds of tables like this then
> you might consider creating some indexed views so that you don't have
> to implement a lot of dynamic code. Take a look at the indexed views
> topic in Books Online.
>
> If on the other hand these hundreds of identical tables are somehow
> being created dynamically at runtime then I be much more hesitant about
> going further. I don't know your system or what it does of course.
> However I'm pretty sure that if confronted with that problem I would
> first write to management in the strongest terms that such an
> architecture is unsustainable and needs to be terminated rather than
> supported.
>

[Back to original message]