Merge two tables with common values by adding the count, append rest

I have two tables, created at runtime, which pulls some data from some entirely different tables and joins and complex where clauses, and finally, I have these two tables with columns something like this:

TableCars:

Id    Company    Views
```````````````````````
01    Honda        12
32    audi         6
18    BMW          3
17    Vector       5

TableBikes:

Id    Company    Views
```````````````````````
01    Honda        3
32    audi         1
19    Kawasaki     2

Note:

company names and id's will always be the same in these two tables, like, honda = 01

I want to merge these two tables in the sense that I dont want any repetitions in the names (or id's) and I want the views to be added. Is there any way to do this without using a while loop and a whole lotta hair loss?

the resultant table should be something like this:

ResultantTable

Id    Company    Views
``````````````````````
01    Honda        15
32    audi         7
18    BMW          3
17    Vector       5
19    Kawasaki     2

Many thanks in advance.

_{ps: I tried to check google, came across "merge" clause, looked up on that, MSDN was way over my开发者_如何学运维 head. I never understand ANYTHING in MSDN. wonder if there are any other people like me.}

You could try this:

SELECT company, SUM(views) FROM (SELECT company, views FROM first UNION ALL SELECT company, views FROM second) as t GROUP BY company

Let me know if you have any issues

select id, company, sum(views) views
from
(
select id, company, views
from tablecars
union all
select id, company, views
from tablebikes
) joined
group by id, company

You can always turn SELECT statements into a CREATE table statement:

select id, company, sum(views) views
into NewTableName
from
(
select id, company, views
from tablecars
union all
select id, company, views
from tablebikes
) joined
group by id, company

I don't normally recommended this method to create permanent tables. It is better to create the table and manually define keys, constraints, defaults, indexes. If you create it this way, you can still add required keys later using ALTER table statements.

If you will always have the two base tables around, and you need this "joined" data for querying/reporting, and it has to keep in sync with the base tables - then what you are really after is a VIEW using the first SELECT statement.