I have a interesting situation that I want to see if there is a solution to...I have 4 tables in 4 databases on the same server, all with the same structure:
dbo.IL_Membership in Pnl_IL_2012 database dbo.NM_Membership in Pnl_NM_2012 database
I want to combine an aggregated dataset from each table into one data set, but I'm trying to make use of the (USE [Pnl_IL_2012] --> Go) Construct to try and grab the data for each aggregate query.
select * from
(USE [Pnl_IL_2012]
GO
select 'IL' as PlanID, sum(Contracts_Hlth) as PROD_Contracts_Hlth from dbo.IL_Membership where [month] between '2012-09-01' and '2012-10-31') Q1
union all
select * from
(USE [Pnl_NM_2012]
GO
select 'NM' as PlanID, sum(Contracts_Hlth) as PROD_Contracts_Hlth from dbo.NM_Membership where [month] between '2012-09-01' and '2012-10-31') Q2
I get these errors:
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'USE'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ')'.
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'USE'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ')'.
Anyone have a way to get union all to work across databases? I don't need a join just a union...
GO signals the end of a batch, so this can't be used in the middle of queries.
Instead, reference the tables using the three part name:
Pnl_IL_2012.dbo.IL_Membership
Giving you the following query:
select 'IL' as PlanID, sum(Contracts_Hlth) as PROD_Contracts_Hlth
from Pnl_IL_2012.dbo.IL_Membership
where [month] between '2012-09-01' and '2012-10-31'
union all
select 'NM' as PlanID, sum(Contracts_Hlth) as PROD_Contracts_Hlth
from Pnl_NM_2012.dbo.IL_Membership
where [month] between '2012-09-01' and '2012-10-31'