[thelist] dynamic table sql question

Brian Cummiskey Brian at hondaswap.com
Mon Apr 25 15:57:04 CDT 2005


Peter Brunone (EasyListBox.com) wrote:
>    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.  

It actually makes perfect sense.  Each Job is from a different client, 
upon which we are calling on THEIR lists for them.

Perhaps I need some sample data to get this together a little better.  I 
don't think anyone is really grasping what i'm dealing with.


So, We have for example 2 clients.
companyA and companyB each with 2 current jobs.

each table has a unique ID to THAT table, as well as a jobid field, 
which is the same as the table name (for all purposes here.. its a 
little different, but thats not an issue i can't handel)

There is also a Master table, called Jobs
which has the Jobid (which is called companyA_1) and holds other 
useless-to-this-post stuff like description, running dates, etc..

basically, i need to select all job ids from jobs.

this will give me

companyA_1
companyA_2
companyB_1
companyA_2


and from those tables, i need to generate the:

select rowID, jobname from <<each of the above table names>>
where phonenumber in ( <<the number list>> )

this will hopefully give me

row	jobname
12345	companyA_1
22345	companyA_2
32345	companyb_1
42345	companyB_2

if there were 4 rows among those tables with the phone numbers i plug in.


Is this making more sense now?

which is why i want to dynmically geenerate my table name that i select 
from.

I thought about doing it in a scripting language... something like

select distinct Jobid from jobs
while NOT rst.eof
	select rowID, jobname from & Jobid & ....
wend

or something like that.  but, i don't need an interface.  i just need to 
run this in query analyizer.



  > Can we assume that the structure (or at least the names of the 
fields) are the same in all these subordinate tables?

No, not all the same...  but the fields i need/care about ARE.

> If so, I recommend the following:
> 
> 1)  First do a select query from systables.  Get all tables with names like 'Job%'.

Well, that's not going to work.  :P  I was using job as an example, but 
they are all different names.  there's no real structure to them other 
than they are less than 8 chars.


> 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.

yeah, so i don't think this is going to work...


>>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?  

the unique id is "smart"
it is setup like COUNTJOBID
so each id i pull can be associated with its proper table.

I'm not expecting many results... 10 tops...  so if i have to update 
manually, it won't be a big deal.   It's just a matter of finding them 
without spending hours trying hunt them down.


>    Did that 5000-foot analysis make any sense?

heh, yes, i followed you- but i don't think its the solution thats going 
to make it work....

thanks though.



More information about the thelist mailing list