[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