[thelist] dynamic table sql question

[Peter Brunone (EasyListBox.com)]

   Well, my first reaction would be "why the heck did they design the database this way", but since I doubt you have any control over that, let's press on.  Can we assume that the structure (or at least the names of the fields) are the same in all these subordinate tables?  If so, I recommend the following:

1)  First do a select query from systables.  Get all tables with names like 'Job%'.

2)  Cycle through this recordset and create a string that contains the update query for that table.

3)  Use sp_executesql (in a stored proc; otherwise just send the string) to execute the query.

   SQL Books Online can tell you how to do this in TSQL; otherwise, you can just slap together a script or assembly in whatever language/environment you choose.

> I plan to run an update query once i have all the rowid's 
> for the records to set the callable status to 'u' for uncallable.

   Wait... how are you going to identify which row ID is in which table?  Do you want to have to find them all over again?  If you need to report the records changed, you'll need to insert the values into a temp table (if you go the stored proc route) or bring them back in an array or hashtable for display on the client (if you go that way).

   Did that 5000-foot analysis make any sense?

 From: Brian Cummiskey Brian at hondaswap.com

Ed McCarroll wrote:
> SELECT * FROM WhateverTable W
> INNER JOIN jobs J ON J.job_name = W.job_name
> WHERE phonenumber IN ( <> )

That's not going to work... I would need to be able to cycle through 
WhateverTable for the entire list of tables in the Jobs table.
I would have to go through all 300+ job tables and change WhaterTable to 
reflect that name.

Thanks for the idea though...

Joshua Olsen Wrote:
> It may be possible to do this in a stored procedure depending on your
> database. Which database are you using?

Sorry, i wasn't clear.
I am using M$SQL 2000 (version 7 i think?)
i can use asp/stored proceudre/triggers if need be, but i don't need to 
export the results to anything. Basically, i just need to get a list of 
the unique ID's from each table in the database where those numbers are 
located. I'm just trying to run the query in Query Analyzier.

A little info about what i'm doing- I work for a B2B call center, and 
we have had some requests to remove from our call list. I need to find 
all instances of these numbers if they exist for other jobs, and set the 
status to 'u' for uncallable in every table. There's a good chance that 
our lists will overlap for big jobs.

Teke at union.com Wrote:
> Union should do the trick.

yes, it would- but that would still require me to manually union all 
300+ tables. Not something i'm looking forward to doing, and human 
error can exist-- theres a good chance i could miss one by accident.

Jay Blanchard Wrote:
> I am not sure that you have provided enough information here or I just
> don't understand, but lets see if we can make order of it. You need to
> check a large number of tables for a list of phone numbers from one
> table? What is your database software and are you using PHP or ASP or
> PERL or anything like that?

No, i need to check if the phone numbers exist in MULTIPLE tables. 
Thus, i want to generate a sub-query that will populate the table names. 
Luckily, i already have a table that holds this information-- that's 
the Jobs table, which holds the JobID, which is the name of the 300+ 
tables i need to look in.

As stated above, this is MS SQL. don't really need a web front end. I 
plan to run an update query once i have all the rowid's for the records 
to set the callable status to 'u' for uncallable.

Thanks for the help so far guys. Let's see if this added info can get 
us anywhere :)