Post reply

using select union all using multiple databases

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

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

  • 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

  • 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

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

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