using select union all using multiple databases

  • 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)

  • 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]

  • 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

  • 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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply