using select union all using multiple databases

• skb 44459

  SSCarpal Tunnel

  Points: 4768

  More actions

  August 11, 2014 at 7:17 am

  #291884

  I would like to create a view for a very complex query and it should run for multiple databases.

  I would not rather qualify database name in the query (somehow I would like to use 'USE Statement' or some alternative )

  Is it possible ?

  create view view1 as

  use db1

  select * from (query tables with multiple joins)

  union all

  use db2

  select * from (query tables with multiple joins)

  use db3

  select * from (query tables with multiple joins)

  use db4

  select * from (query tables with multiple joins)

• LutzM

  SSC Guru

  Points: 107049

  More actions

  August 11, 2014 at 10:43 am

  #1736941

  You can't create views across database using the syntax you're thinking of.

  Instead you could create a view on each database and use that in your view:

  create view view1 as

  use

  select * from db1.schema1.view1

  union all

  select * from db2.schema1.view1

  union all

  select * from db3.schema1.view1

  union all

  select * from db4.schema1.view1

  Make sure you vae permissions to select from those views and matching columns.

  ---

  
  Lutz
  A pessimist is an optimist with experience.

  How to get fast answers to your question[/url]
  How to post performance related questions[/url]
  Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

• Evil Kraig F

  SSC Guru

  Points: 100851

  More actions

  August 11, 2014 at 11:41 am

  #1736976

  I would disagree. I think the better approach would be the OP's original idea, but slightly modified. If you use SELECT * FROM OPENQUERY( LinkedServer, 'sqlstatement'), you'll have (hopefully) a lot less impact across the servers trying to meld all that together on the local box.

  I would weep for my servers if my peers did a view like described above though, in just about any form. That would need some wicked justifications... and a few explanations about why we aren't ETL'ing into a warehouse somewhere.

  ---

  - Craig Farrell

  Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

  For better assistance in answering your questions[/url] | Forum Netiquette
  For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
  Twitter: @AnyWayDBA

• Jason Wolfkill

  SSCrazy Eights

  Points: 9832

  More actions

  August 12, 2014 at 12:56 pm

  #1737325

  Evil Kraig F (8/11/2014)

  ---

  I would disagree. I think the better approach would be the OP's original idea, but slightly modified. If you use SELECT * FROM OPENQUERY( LinkedServer, 'sqlstatement'), you'll have (hopefully) a lot less impact across the servers trying to meld all that together on the local box.

  I would weep for my servers if my peers did a view like described above though, in just about any form. That would need some wicked justifications... and a few explanations about why we aren't ETL'ing into a warehouse somewhere.

  Assuming the OP used the term "database" in its correct SQL Server sense, I think Lutz's solution should work just fine - a view that returns data from multiple databases on the same SQL Server instance is usually not a problem.

  If, on the other hand, the OP wants to create a view that returns data from different *instances* of SQL Server, well, yuck - that's a problematic idea. I think Kraig's idea of using a series of SELECT * FROM OPENQUERY() statements with UNION ALLs to get the data from each instance would be the best way to go here.

  Jason Wolfkill