How to merge multiple tables in SQL query

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

SQL Server 2000 Forums

Transact-SQL (2000)

How to merge multiple tables in SQL query

Author

Topic

rhst11
Starting Member

3 Posts

Posted - 2009-12-11 : 05:59:53

Hi, I'm new to SQL. I have 3 queries created deparately and finding a way to merge them together.

Table 1:
ID Name Total_X
1 abc 22
12 bcd 5
6 tfg 11

Table 2:
ID Name Total_y
4 otg 50
1 abc 13
9 eft 21

Table 3:
ID Name Total_Z
10 utt 4
6 tfg 45
5 ddf 19

After the merge of tables should look like this, any help?
Merged table:
ID Name Total_X Total_y Total_z
1 abc 22 13 Null
4 otg Null 50 Null
5 ddf Null Null 19
6 tfg 11 Null 45
9 eft Null 21 Null
10 utt Null Null 4
12 bcd 5 Null Null

crö
Starting Member

6 Posts

Posted - 2009-12-11 : 08:05:37

Hi

Something like that:


INSERT MergeTable (ID, Total_X, Total_Y, Total_Z)
   SELECT ID, Total_X, NULL, NULL
   FROM Table_1

INSERT MergeTable (ID, Total_X, Total_Y, Total_Z)
   SELECT ID, NULL, Total_Y, NULL
   FROM Table_2

INSERT MergeTable (ID, Total_X, Total_Y, Total_Z)
   SELECT ID, NULL, NULL, Total_Z
   FROM Table_3

When your row "ID" is an IDENTITY-row, you have to set IDENTITY_INSERT on for the merged table:


SET IDENTITY_INSERT MergeTable ON
[insert statements...]
SET IDENTITY_INSERT MergeTable OFF

Good luck!
crö

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2009-12-11 : 08:58:30

I have a slightly different interpretation of the requirements...

declare @Table1 table (ID int, Name varchar(9), Total_X int)
insert @Table1
          select 1, 'abc', 22
union all select 12, 'bcd', 5
union all select 6, 'tfg', 11

declare @Table2 table (ID int, Name varchar(9), Total_Y int)
insert @Table2
          select 4, 'otg', 50
union all select 1, 'abc', 13
union all select 9, 'eft', 21

declare @Table3 table (ID int, Name varchar(9), Total_Z int)
insert @Table3
          select 10, 'utt', 4
union all select 6, 'tfg', 45
union all select 5, 'ddf', 19

select ID, Name, max(Total_X) as Total_X, max(Total_Y) as Total_Y, max(Total_Z) as Total_Z
--into MyNewTable --uncomment this to put into a new table
from (
              select ID, Name, Total_X, null as Total_Y, null as Total_Z from @Table1
    union all select ID, Name, null, Total_Y, null from @Table2
    union all select ID, Name, null, null, Total_Z from @Table3) a
group by ID, Name
order by id

Ryan Randall - Yak of all trades
Solutions are easy. Understanding the problem, now, that's the hard part.

rhst11
Starting Member

3 Posts

Posted - 2009-12-11 : 22:00:16

I found a very good way now. Have a look at this:

SELECT COALESCE(a.ID, b.ID, c.ID),
COALESCE(a.NAME, b.NAME, c.NAME),
Total_X,
Total_Y,
Total_Z
FROM #1 a
FULL OUTER JOIN #2 b
ON a.ID = b.ID
FULL OUTER JOIN #3 c
ON a.ID = c.ID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources