Posted by Terry Kreft on 11/30/05 14:57

This would be a nice solution with a reasonable number of tables but as the
OP said " There are literally hundreds of these tables ...", I thought
concatenation of a SQL string would be a better solution.


--
Terry Kreft



"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
news:Xns971E6DF79130AYazorman@127.0.0.1...
> Steve (sjc4914@yahoo.com) writes:
>> 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?
>
> David's suggestion of a view is a possible way, although I don't think it
> can be an indexed view, as indexed views can not include the UNION
> keyword.
>
> The view definition would look like:
>
> SELECT tablename = 'tbl1', * FROM tbl1
> UNION ALL
> SELECT tablename = 'tbl2', * FROM tbl2
> ...
>
> When you query the view as:
>
> SELECT ...
> FROM yourview
> WHERE tablename= 'tbl2'
> AND ...
>
> I would expect SQL Server to be smart to not access the other tables.
> They would appear in the query plan, but a startup expression would
> prevent
> any access to the tables.
>
> The other option beside dynamic SQL would be to write a small macro
> processor that reads a common stub, and the substitutes the table name,
> so that you have one procedure per table.
>
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]