CODEWITHSUNDEEP
sqlmerge
Middletone
Middletone

Reputation: 4270

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

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

Upvotes: 0

Views: 33014

Answers (5)

Tordek
Tordek

Reputation: 10872

Perhaps you're looking for UNION?

IE:

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

Upvotes: 2

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171391

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

Upvotes: 0

Chris
Chris

Reputation: 6760

To do it, you would go:

SELECT * INTO TABLE3 FROM TABLE1
UNION
SELECT * FROM TABLE2

Provided both tables have the same columns

Upvotes: 0

John Fouhy
John Fouhy

Reputation: 42183

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)

Upvotes: 1

Darrel Miller
Darrel Miller

Reputation: 142014

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

Upvotes: 0

Related Questions