Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sqlclueless
Starting Member
4 Posts |
 Posted - 2006-01-28 : 18:54:02
|
Roadblock #2 for the day.  ...To piggyback on my previous [url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=60963[/url] issue, I need to create a similar view, only across multiple database. Each school has their own database, but have the same tables. I triedSELECT SUM(Database1.dbo.Entries) as [Total Entries], SUM(Database1.dbo.Graduates) as [Total Graduates] FROM myTable GROUP BY ClassNameUNION ALLSELECT SUM(Database2.dbo.Entries) as [Total Entries], SUM(Database2.dbo.Graduates) as [Total Graduates] FROM myTable GROUP BY ClassNameBut that only returns data from the first database. UNION ALL is the only thing that I can find so far, to pull from multiple database.Thanks in advance! |
|
|
nathans
Aged Yak Warrior
938 Posts |
Posted - 2006-01-28 : 20:54:19
|
| To access tables in different databases you need to specify the db name in the table name.... am I missing something here?select yourColumnfrom db1.dbo.yourTableunion allselect yourColumnfrom db2.dbo.yourTableNathan Skerl |
 |
|
|
sqlclueless
Starting Member
4 Posts |
Posted - 2006-01-28 : 21:04:29
|
Doh! Thank you Nathan! |
|
|
|
nathans
Aged Yak Warrior
938 Posts |
Posted - 2006-01-28 : 21:33:31
|
| glad to help :)Nathan Skerl |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-01-29 : 05:02:42
|
| Are the data segregated in some specific way between the tow database? For example "2005" in one and "2006" in the other?Because if so you can give SQL Server some additional "hints" in these type of horizontal-partitioning views such that when the WHERE clause you use on your View only references "2006" all the other UNIONs are ignored - which is obviously good for performance!Sorry if you already know this.Kristen |
|
|
|
|
|
|
Views across multiple databases