|
|
Posted by calan on 09/21/05 23:52
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
[Back to original message]
|