| Author |
Topic |
|
pcawley
Starting Member
7 Posts |
 Posted - 2009-02-09 : 13:03:34
|
| Is there an easy way to query data from an ever-growing list of tables?The application that stores the data for the parts we make basically creates a new data table for each month - ex. widgets200901, widgets200902, and so on. These tables are structurally exactly the same, there is just an ever growing list of them. What I need to do is run a query on this list of tables as if they were one big table, and without having to constantly having to rewrite the query for the new tables created each month.Unfortunately I have no control over the application or how it stores its data.Thank you in advance for any help. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-02-09 : 13:06:14
|
| why should you create a new table each time? it would be enough to store them all in single table with a datefield to indicate month deatil. or if table is large, you may even look for splitting it into partitions. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-02-09 : 13:07:40
|
| for your current design ,what you could do is to make a view out of tables and add each of new tables to it once created |
|
|
|
pcawley
Starting Member
7 Posts |
Posted - 2009-02-09 : 13:09:35
|
| I would heartily agree, but this is an application we purchased from a vendor and I have no control over how it stores the data. I have just been tasked with extracting reports from it. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-02-09 : 13:12:45
|
| then add each table to your view after creation. |
|
|
|
pcawley
Starting Member
7 Posts |
Posted - 2009-02-09 : 13:17:42
|
| I will have to check with my boss and the vendor to see if they will even let me create a new view in the database.Is there any other way? |
|
|
|
pcawley
Starting Member
7 Posts |
Posted - 2009-02-09 : 13:46:55
|
| Don't think the view is going to work. I tried this on a test database and got back the error "Column names in each view or function must be unique. Column name "DocNo" in view or function "dbo.xxxxxx" is specified more than once."Is there a specific way I need to structure this? |
|
|
|
pcawley
Starting Member
7 Posts |
Posted - 2009-02-09 : 13:50:50
|
| The default join on this view is an INNER JOIN, should I be using a UNION? |
|
|
|
pcawley
Starting Member
7 Posts |
Posted - 2009-02-09 : 15:37:11
|
| Yeah, looks like the UNION got it. Would still like to know if there is a good way to do this without creating the View. Thanks! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-02-09 : 22:44:32
|
quote:
Originally posted by pcawley
Don't think the view is going to work. I tried this on a test database and got back the error "Column names in each view or function must be unique. Column name "DocNo" in view or function "dbo.xxxxxx" is specified more than once."Is there a specific way I need to structure this?
thats because you have same column coming from more than one table in view definition. in such cases you must use column aliases to avoid ambiguity |
|
|
|
sridhar.dbe
Starting Member
34 Posts |
Posted - 2009-02-09 : 23:57:52
|
| hi, is this what u want ?declare @v_sql varchar(max)select @v_sql = ''select @v_sql = @v_sql + ';select * from '+table_namefrom information_schema.tables where table_name like'%web%'select @v_sql = stuff(@v_sql, 1, 1, '')execute(@v_sql)you told that, you don't know the table names so ihave used 'information_schema.tables ' view |
|
|
|
pcawley
Starting Member
7 Posts |
Posted - 2009-02-10 : 07:40:23
|
quote:
Originally posted by sridhar.dbe
hi, is this what u want ?declare @v_sql varchar(max)select @v_sql = ''select @v_sql = @v_sql + ';select * from '+table_namefrom information_schema.tables where table_name like'%web%'select @v_sql = stuff(@v_sql, 1, 1, '')execute(@v_sql)you told that, you don't know the table names so ihave used 'information_schema.tables ' view
Thanks! I'll give this a try in the next couple days and post back as to whether I got it to work. |
|
|
|
|
Query from dynamic list of multiple tables