Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2007-01-04 : 09:42:36
|
| Joe writes "I have a table which lists all Operational Data tables in our database (there are hundreds). I have a working query which performs a block of code on a specified table. This same query needs to be run on all tables listed in my first table. Is there an easy way to cycle through the query 'n' times for 'n' relevant tables? I've tried several methods and can get a cycle to work through all the tables listed, but when I try to place a reference to the table instead of a specific table I get various errors depending on how I try to implement. I'm sure this can be done; I'm just too green to SQL Server to know how!I didn't see any applicable articles in forums or in back-dated stuff back to 2000. But I'm sure my search wasn't thorough...SQL Server vs 8.00.2039 Query AnalyzerWindows XP Pro SP 2Thanks," |
|
|
cognos79
Posting Yak Master
241 Posts |
Posted - 2007-01-04 : 11:35:29
|
| can you not construct a dynamic sql statement and when you loop through each table you can change the table name |
 |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2007-01-04 : 15:45:34
|
quote:
Originally posted by AskSQLTeam
Joe writes "I have a table which lists all Operational Data tables in our database (there are hundreds). I have a working query which performs a block of code on a specified table. This same query needs to be run on all tables listed in my first table. Is there an easy way to cycle through the query 'n' times for 'n' relevant tables? I've tried several methods and can get a cycle to work through all the tables listed, but when I try to place a reference to the table instead of a specific table I get various errors depending on how I try to implement. I'm sure this can be done; I'm just too green to SQL Server to know how!I didn't see any applicable articles in forums or in back-dated stuff back to 2000. But I'm sure my search wasn't thorough...SQL Server vs 8.00.2039 Query AnalyzerWindows XP Pro SP 2Thanks,"
The bad news? you'll need to use a hack like looping through the necessary tables and then dynamically constructing and then executing SQL statements, as cognos79 mentions.The good news? you have learned first-hand why you should normalize your databases and insist on a proper design. with a properly designed database, your data would be stored in 1 table and a single SQL statement would most likely be all you need ..... The amount of time necessary to redesign your system from scratch properly and then write your stored procedures would probably be less than the time necessary two write, test, debug and troubleshoot one or two stored procedures using the existing design.- Jeff |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
|
|
Referencing tables in queries