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 |
|
dcuffee
Starting Member
3 Posts |
 Posted - 2013-03-27 : 14:31:13
|
| I am trying to do a query that I am not sure can be written without using a cursor and building dynamically. But if someone can help me figure out how to do it without having to use cursors that would be awesome.So I have a table called AppSystem.ApplianceTypes. It has a varchar column named ApplianceTypeTableName that contains the name of other tables in the form of schema.tablename. There are 71 rows of tablenames in the AppSystem.ApplianceTypes table. Each of the 71 tables have a column inside them called MFG. What I want to do is do a query that lists the MFG values in all 71 tables in the same query (like a UNION). By the way, the 71 rows in AppSystem.ApplianceTypes will grow in the future as we add new tables.If someone can figure this out, I will be praising them as a SQL King/Queen. :) If you don't think it can be done without cursors just let me know and I will figure that part out using the cursor.Thanks,David |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2013-03-27 : 15:36:54
|
| select 'select MFG from ' + ApplianceTypeTableName + 'union all 'from AppSystem.ApplianceTypesRun that and copy/paste the output into a new window. Remove the last "union all". If you need to do this programmatically instead, you'll need dynamic SQL.And this is not a good database design. I would recommend fixing it now rather than waiting as queries it's likely your queries are going to need to be more and more complex due to this design problem.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
|
|
|
|
SELECT FROM Multiple tables with names in a table