Merge two tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Merge two tables

Author

Topic

nunov
Starting Member

17 Posts

Posted - 2010-05-11 : 20:42:48

I have to tables with the same schema and I want to merge them into one.
How can I do that?

NV

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-05-11 : 20:57:31

Just use UNION ALL operator like this:

SELECT * FROM table_1
UNION ALL
SELECT * FROM table_2

nunov
Starting Member

17 Posts

Posted - 2010-05-11 : 21:07:09

it's not working.

I have these two tables:

select p.numutil as utilizador2, p.numinq as inquerito2, sum(t.pontosok) as pontostxtcerto
from utilresptxt ut, utilpart p, perguntatxt t
where p.numinq=ut.numinq
and p.numutil=ut.numutil
and ut.numinq=t.numinq
and ut.numperg=t.numperg
and ut.resposta=t.respcerta
group by p.numutil, p.numinq

select p.numutil as utilizador3, p.numinq as inquerito3, sum(t.pontoserr) as pontostxterrado
from utilresptxt ut, utilpart p, perguntatxt t
where p.numinq=ut.numinq
and p.numutil=ut.numutil
and ut.numinq=t.numinq
and ut.numperg=t.numperg
and ut.resposta!=t.respcerta
group by p.numutil, p.numinq

what I want to do is merge them in a table that sums pontostextoerrado and pontostextocerto when utilizador2=utilizador3 and inquerito2=inquerito3.

when there is a record in one table that doesn't have another one in the other table where utilizador2=utilizador3 and inquerito2=inquerito3, I also insert it

Did you get it?

NV

nunov
Starting Member

17 Posts

Posted - 2010-05-11 : 22:57:24

Please i need some help on this

NV

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-05-12 : 20:20:56

[code]select coalesce(t1.utilizador2, t2.utilizador3) as utilizador
, coalesce(t1.inquerito2, t2.inquerito3) as inquerito
, pontostxtcerto
, pontostxterrado
from
(
select p.numutil as utilizador2,
p.numinq as inquerito2,
sum(t.pontosok) as pontostxtcerto
from utilresptxt ut, utilpart p, perguntatxt t
where p.numinq=ut.numinq
and p.numutil=ut.numutil
and ut.numinq=t.numinq
and ut.numperg=t.numperg
and ut.resposta=t.respcerta
group by p.numutil, p.numinq
)t1 full outer join
(
select p.numutil as utilizador3,
p.numinq as inquerito3,
sum(t.pontoserr) as pontostxterrado
from utilresptxt ut, utilpart p, perguntatxt t
where p.numinq=ut.numinq
and p.numutil=ut.numutil
and ut.numinq=t.numinq
and ut.numperg=t.numperg
and ut.resposta!=t.respcerta
group by p.numutil, p.numinq
)t2 on t1.utilizador2 = t2.utilizador3
and t1.inquerito2 = t2.inquerito3;[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources