How do I merge data from two tables in a single database call into the same columns?

Question

If I run the two statements in batch will they return one table to two to my sqlcommand object with the data merged. What I am trying to do is optimize a search by searching twice, the first time on one set of data and then a second on another. They have the same fields and I’d like to have all the records from both tables show and be added to each other. I need this so that I can sort the data between both sets of data but short of writing a stored procedure I can’t think of a way of doing this.

Eg. Table 1 has columns A and B, Table 2 has these same columns but different data source. I then wan to merge them so that if a only exists in one column it is added to the result set and if both exist it eh tables the column B will be summed between the two.

Please note that this is not the same as a full outer join operation as that does not merge the data.

[EDIT]

Here's what the code looks like:

Select * From (Select ID,COUNT() AS Count From [Table1]) as T1 full outer join (Select ID,COUNT() AS Count From [Table2]) as T2 on t1.ID = T2.ID

Answer 1

I'm not sure if I understand completely but you seem to be asking about a UNION

SELECT A,B
FROM tableX
UNION ALL
SELECT A,B
FROM tableY

Answer 2

Perhaps you're looking for UNION?

IE:

SELECT A, B FROM Table1
UNION
SELECT A, B FROM Table2

Answer 3

Possibly:

select table1.a, table1.b
from table1
where table1.a not in (select a from table2)
union all
select table1.a, table1.b+table2.b as b
from table1
inner join table2 on table1.a = table2.a

edit: perhaps you would benefit from unioning the tables before counting. e.g.

select id, count() as count from
(select id from table1
 union all
 select id from table2)

Answer 4

To do it, you would go:

SELECT * INTO TABLE3 FROM TABLE1
UNION
SELECT * FROM TABLE2

Provided both tables have the same columns

Answer 5

I think what you are looking for is this, but I am not sure I am understanding your language correctly.

select id, sum(count) as count
from (
    select id, count() as count
    from table1
    union all
    select id, count() as count
    from table2
) a
group by id