0

Trying to create a view where all the tables have the same fields but different data in them. We have a table that we track each of these tables, and track which ones are active. So I want a view that will tie all the active tables together in one view.

Say you have tables A, B, and C. Only B and C are active, table 1 lists each table and which is active. So form table 1 we can get the table names we need for the view.

It escapes me on how to do this on the SQL Server (2008 version)

Charles Strange
  • 1
  • This is really bad design. Could you give an example of what you really try to do and we might be able to help you find a better DB design in which you don't need dynamic queries – juergen d Aug 16 '15 at 16:36

2 Answers2

0

This does sound like a bad design. It is better to store all the data in one table and use partitioning methods to access it.

Here, though, is one method to do what you want:

create view abc as
    select a.*
    from a cross join
         (select 1 from table1 where status = 'active' and tablename = 'a')
    union all
    select b.*
    from b cross join
         (select 1 from table1 where status = 'active' and tablename = 'b')
    select c.*
    from c cross join
         (select 1 from table1 where status = 'active' and tablename = 'c');

I don't really recommend this approach. It is intended simply to show that it is possible. I chose cross join, because the subquery is evaluated only once and this might be an optimization for the underlying execution code.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

Maybe you need to use a table partition: What is a table partition?

Here is an example:MSDN

Community
  • 1
  • 1
Luis Teijon
  • 4,769
  • 7
  • 36
  • 57