Microsoft SQL Server forum - searching a list of tables, derived from another table

Author

searching a list of tables, derived from another table

calan

2005-09-21, 8:23 pm

Relative SQL newbie here......this is probably easy, but....

Lets say I have a table (MainTable) that stores a list of input table names,
a primary key (PKey), and a field called "Configured" for each one. Each of
these input tables also contain a field called "Configured", which is set to
true or false in another process based on an OrderNumber. (So an order's
inputs are stored in several input tables, and the MainTable is a summary
table that shows which input tables have been configured for any given
OrderNumber).

What I need to do is open each input table, and look for a record containing
a specific OrderNumber and where Configured=true. If a record is found, I
need to update the Configured field for that table in the MainTable, and
then move on to the next sub-table.

The way I'm doing it now is with simple SQL and loops. Here is the basic
code (ASP):

********************
********************
*

OrderNumber = "562613" ' the current order that is being processed

' reset all configured flags
sql = "UPDATE MainTable SET Configured = 0"
conn.execute sql, , &H00000080

' get list of all tablenames
sql = "SELECT InputTableName, PKey FROM MainTable WHERE InputTableName <>
'---'"
set rsTableNames = conn.execute(sql)
while not rsTableNames.eof
' test each input table for configured flag
sql = "SELECT Configured FROM " & rsTableNames("InputTableName")
& _
" WHERE Configured = 1 AND OrderNumber = '" & OrderNumber &
"'"
set rs = conn.execute(sql)

If Not rs.EOF Then
' update the main table
sql = "UPDATE MainTable SET Configured = 1 WHERE PKey='" &
rsTableNames("PrimaryKey") & "'"
conn.execute sql, , &H00000080
end if
set rs = nothing
rsTableNames.movenext
wend

********************
********************
*

There has to be a faster way.. I think.... maybe something that could be
written as a stored procedure? I use a similar technique in a couple of
other places and it's a bit of a performance hit, especially as the number
of input tables grows.

TIA!

Calan

Erland Sommarskog

2005-09-21, 8:23 pm

calan (none@nospam.com) writes:
> Relative SQL newbie here......this is probably easy, but....
>
> Lets say I have a table (MainTable) that stores a list of input table
> names, a primary key (PKey), and a field called "Configured" for each
> one. Each of these input tables also contain a field called
> "Configured", which is set to true or false in another process based on
> an OrderNumber. (So an order's inputs are stored in several input
> tables, and the MainTable is a summary table that shows which input
> tables have been configured for any given OrderNumber).
>
> What I need to do is open each input table, and look for a record
> containing a specific OrderNumber and where Configured=true. If a record
> is found, I need to update the Configured field for that table in the
> MainTable, and then move on to the next sub-table.

What is the purpose of all these input tables?

This is a somewhat unorthodox design. A table with table names to process,
is not the normal SQL idiom, at least not for an order system. (For a
database maintenance job it could be.)

You could certainly improve performace by putting the code into a stored
procedure, because you would be saved all the network roundtrips. But the
procedure should not really have a loop over the input table, rather it
should have an UPDATE statement per table.

Table are very much individual objects in SQL Server. Each object has its
set of statistics and indexes, which can result in two similar queries
on two tables can get very different query plans.

Now, I don't know your tables at all, but it is not uncommon when people
have several tables likes, that these tables should in fact be one
single table with one key added.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

calan

2005-09-21, 8:23 pm

<snip>

> What is the purpose of all these input tables?

This is basically an engineering configurator for a very complex product.
Each input table holds data for a specific "component" of the product.

Using the analogy of a car, a table called "Interior" may have fields such
as "Seat Color", "Stereo Type", "Number Of Speakers", and maybe tens or
hundreds more... A Suspension table could have "Wheel Size", "Spring Type",
"Load Rating", "Brake Type", etc etc.

If you have 100 components with maybe 30-50 configuration questions for each
one..and then multiple products tied into the same system...... well....
you end up with a massive number of input fields in one table to say the
least... Also, I need the ability to work on individual components with
seperate apps, so the multiple table approach seemd to make sense.

I'm always open for suggestions though :-)

Thanks

Erland Sommarskog

2005-09-22, 8:24 pm

calan (none@nospam.com) writes:
>
> This is basically an engineering configurator for a very complex product.
> Each input table holds data for a specific "component" of the product.
>
> Using the analogy of a car, a table called "Interior" may have fields
> such as "Seat Color", "Stereo Type", "Number Of Speakers", and maybe
> tens or hundreds more... A Suspension table could have "Wheel Size",
> "Spring Type", "Load Rating", "Brake Type", etc etc.
>
> If you have 100 components with maybe 30-50 configuration questions for
> each one..and then multiple products tied into the same system......
> well.... you end up with a massive number of input fields in one table
> to say the least... Also, I need the ability to work on individual
> components with seperate apps, so the multiple table approach seemd to
> make sense.

OK. To be perfectly honest, I still have a kind of feeling that the
arrangement is not the best. But I would have to looking into business
requirements etc to tell for sure. But that would maybe then be
with the placement of all these Congifured flags. Having separate tables
certainly makes sense, since else you would get a very wide table, as
I understand it.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

calan

2005-09-22, 8:24 pm

<snip>

So back to the original question, is there an SQL example of this? I'm not
having any luck finding anything on using a subquery in a FROM clause that
returns a list of table names.

I thought something simple like this would work (This assumes that all
tables listed in "MyTables" will have a field called "Configured".):

SELECT t2.TableName, t1.Configured
FROM (SELECT TableName FROM MyTables AS t2) t1

But it doesn't....

--CELKO--

2005-09-23, 3:23 am

>> If you have 100 components with maybe 30-50 configuration questions for each one..and then multiple products tied into the same system...... well.... you end up with a massive number of input fields [sic] in one table to say the least... <<

Have you thought about a using a parts explosion/tree model? All the
options would be subordinates under a component in a master model that
gets traversed and pruned as decisons are made. You are looking at a
tree with 5000 nodes and you can use the hierarchy to enforce some
dependencies. I.e. If you get a radio, then you have to pick from five
models, if you do not get a radio, we do not show the models.

Erland Sommarskog

2005-09-23, 8:23 pm

calan (none@nospam.com) writes:
> So back to the original question, is there an SQL example of this? I'm
> not having any luck finding anything on using a subquery in a FROM
> clause that returns a list of table names.

And you will never have. There is no indirection on table names in T-SQL.
In fact there is very little indirection at all in T-SQL. And for a good
reason.

> I thought something simple like this would work (This assumes that all
> tables listed in "MyTables" will have a field called "Configured".):
>
> SELECT t2.TableName, t1.Configured
> FROM (SELECT TableName FROM MyTables AS t2) t1
>
> But it doesn't....

Either you loop that table and build the list with dynamic SQL, or
you hard-code each table. I would recommend the latter.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Sponsored Links