Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

using select union all using multiple databases Expand / Collapse
Author
Message
Posted Monday, August 11, 2014 7:17 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, December 5, 2014 1:07 PM
Points: 103, Visits: 241
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)
Post #1601846
Posted Monday, August 11, 2014 10:43 AM This worked for the OP Answer marked as solution


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:28 PM
Points: 6,880, Visits: 13,466
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
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1601957
Posted Monday, August 11, 2014 11:41 AM This worked for the OP Answer marked as solution


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, December 15, 2014 2:26 PM
Points: 5,466, Visits: 7,647
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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1601996
Posted Tuesday, August 12, 2014 12:56 PM This worked for the OP Answer marked as solution


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 12:50 PM
Points: 1,061, Visits: 2,580
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
Blog: SQLSouth
Twitter: @SQLSouth
Post #1602423
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse