Retrieving Data from current DB and archived DB

Question

With one query,I want to fetch "COLLECTIVE/COMBINED" data from current DB & archived DB with multiple conditions and several joins.

SELECT * FROM [Database].[dbo].[Current_DB_TableName] where ....... UNION ALL SELECT * FROM [Databse].[dbo].[Archive_DB_TableName] where .......

Do I have to copy paste the first select statement and second select statement with only difference of different database name?? (Assuming only database name of archived tables are different)

Answer 1

Yes, since all that UNION ALL does is it combines two result sets. It's not required to use the same filters or joins, though, but the results might be quite surprising if you don't.

Let's see an example. Before you run this, try to figure out what the result set is going to be. We are clearly looking for rows that don't have content as xx or yy, right?

if object_id('tempdb..#foo') is not null
drop table #foo
if object_id('tempdb..#bar') is not null
drop table #bar
create table #foo(id int, content nvarchar(64));
create table #bar(id int, content nvarchar(64));

insert #foo (id, content) values (1, 'a'), (3, 'c'), (5, 'xx');
insert #bar (id, content) values (2, 'b'), (4, 'd'), (6, 'yy');


select * from #foo where content <> 'xx' and content <> 'yy'
union all
select * from #bar;

select * from #foo where content <> 'xx' and content <> 'yy'
union all
select * from #bar where content <> 'xx' and content <> 'yy';

So, what's the output from the first query, this?

1   a
3   c
2   b
4   d
6   yy

or this? Should both queries return the same result set?

1   a
3   c
2   b
4   d

Since the select * from #bar has no where clause, it will return all the rows. The first query's where clause has no effect notsoever on the second query's result set.