Merging two tables values into one table

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

SQL Server Development (2000)

Merging two tables values into one table

Author

Topic

pras2007
Posting Yak Master

216 Posts

Posted - 2008-01-30 : 00:16:18

Hello All,

I have two tables with three columns that I want combined into one table result. The final table should have four columns instead of three. Does anyone know how to accomplish this task? An illustration is shown below for better clarification. Please advice. Thanks.

TABLE 1
Col1 Col2 Col3
1/8/2008 RED 6
1/9/2008 RED 10
1/7/2008 WHITE 2
1/7/2008 BLUE 6
1/8/2008 BLUE 5
1/9/2008 BLUE 8

TABLE 2
Col1 Col2 Col3
1/7/2008 RED 13
1/8/2008 RED 16
1/9/2008 RED 22
1/7/2008 WHITE 6
1/8/2008 WHITE 5
1/9/2008 WHITE 2
1/10/2008 WHITE 3
1/7/2008 BLUE 20
1/8/2008 BLUE 11
1/9/2008 BLUE 15

FINAL TABLE
Col1 Col2 Col3 Col4
1/7/2008 RED 13
1/8/2008 RED 6 16
1/9/2008 RED 10 22
1/7/2008 WHITE 2 6
1/8/2008 WHITE 5
1/9/2008 WHITE 2
1/10/2008 WHITE 3
1/7/2008 BLUE 6 20
1/8/2008 BLUE 5 11
1/9/2008 BLUE 8 15

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-30 : 00:37:55

[code]INSERT INTO FinalTable(Col1,Col2,Col3,Col4)
SELECT t1.Col1,t1.Col2,t1.Col3,NULL
FROM Table1 t1
LEFT OUTER JOIN Table2 t2
ON t2.Col1 =t1.Col1
AND t2.Col2=t1.Col2
WHERE t2.Col1 IS NULL

UNION ALL

SELECT t2.Col1,t2.Col2,t2.Col3,NULL
FROM Table2 t2
LEFT OUTER JOIN Table1 t1
ON t2.Col1 =t1.Col1
AND t2.Col2=t1.Col2
WHERE t1.Col1 IS NULL

UNION ALL

SELECT t1.Col1,t1.Col2,t1.Col3,t2.Col3
FROM Table2 t2
INNER JOIN Table1 t1
ON t2.Col1 =t1.Col1
AND t2.Col2=t1.Col2[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-30 : 00:57:30

or even:-

DECLARE @table1 TABLE 
(
Col1 datetime,
Col2 varchar(10),
 Col3 int)

DECLARE @table2 TABLE 
(
Col1 datetime,
Col2 varchar(10),
 Col3 int)
 INSERT INTO @table1 values('1/8/2008','RED',6)
INSERT INTO @table1 values('1/9/2008', 'RED' ,10) 
INSERT INTO @table1 values('1/7/2008', 'WHITE', 2 )
INSERT INTO @table1 values('1/7/2008', 'BLUE', 6 )
INSERT INTO @table1 values('1/8/2008', 'BLUE', 5 )
INSERT INTO @table1 values('1/9/2008', 'BLUE', 8 )
 
INSERT INTO @table2 values('1/7/2008', 'RED', 13) 
INSERT INTO @table2 values('1/8/2008', 'RED', 16) 
INSERT INTO @table2 values('1/9/2008', 'RED', 22 )
INSERT INTO @table2 values('1/7/2008', 'WHITE', 6 )
INSERT INTO @table2 values('1/8/2008', 'WHITE', 5 )
INSERT INTO @table2 values('1/9/2008', 'WHITE', 2 )
INSERT INTO @table2 values('1/10/2008', 'WHITE', 3 )
INSERT INTO @table2 values('1/7/2008', 'BLUE', 20) 
INSERT INTO @table2 values('1/8/2008', 'BLUE', 11 )
INSERT INTO @table2 values('1/9/2008', 'BLUE', 15 )


SELECT ISNULL(t1.Col1,t2.Col1) AS Col1,
       ISNULL(t1.Col2,t2.Col2) AS Col2,
       ISNULL(t1.Col3,t2.Col3) AS Col3,
       CASE WHEN t1.Col3 IS NULL THEN NULL ELSE t2.Col3 END AS Col4
INTO #Final
FROM @table1 t1
FULL OUTER JOIN @table2 t2
ON t1.Col1=t2.Col1
AND t1.Col2=t2.Col2
ORDER BY Col2,Col1

select * FROM #Final
output
----------------
Col1                    Col2       Col3        Col4
----------------------- ---------- ----------- -----------
2008-01-07 00:00:00.000 BLUE       6           20
2008-01-08 00:00:00.000 BLUE       5           11
2008-01-09 00:00:00.000 BLUE       8           15
2008-01-07 00:00:00.000 RED        13          NULL
2008-01-08 00:00:00.000 RED        6           16
2008-01-09 00:00:00.000 RED        10          22
2008-01-07 00:00:00.000 WHITE      2           6
2008-01-08 00:00:00.000 WHITE      5           NULL
2008-01-09 00:00:00.000 WHITE      2           NULL
2008-01-10 00:00:00.000 WHITE      3           NULL

pras2007
Posting Yak Master

216 Posts

Posted - 2008-01-30 : 01:20:52

visakh16,

Thanks for the quick reply. That was exactly what I was looking for.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-30 : 01:22:26

You are welcome pras2007

pras2007
Posting Yak Master

216 Posts

Posted - 2008-01-30 : 08:47:00

visakh16,

The Null values suppose to be in col3 not col4, can you revise? Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-30 : 08:53:54

quote:
Originally posted by pras2007

visakh16,

The Null values suppose to be in col3 not col4, can you revise? Thanks.

DECLARE @table1 TABLE 
(
Col1 datetime,
Col2 varchar(10),
 Col3 int)

DECLARE @table2 TABLE 
(
Col1 datetime,
Col2 varchar(10),
 Col3 int)
 INSERT INTO @table1 values('1/8/2008','RED',6)
INSERT INTO @table1 values('1/9/2008', 'RED' ,10) 
INSERT INTO @table1 values('1/7/2008', 'WHITE', 2 )
INSERT INTO @table1 values('1/7/2008', 'BLUE', 6 )
INSERT INTO @table1 values('1/8/2008', 'BLUE', 5 )
INSERT INTO @table1 values('1/9/2008', 'BLUE', 8 )
 
INSERT INTO @table2 values('1/7/2008', 'RED', 13) 
INSERT INTO @table2 values('1/8/2008', 'RED', 16) 
INSERT INTO @table2 values('1/9/2008', 'RED', 22 )
INSERT INTO @table2 values('1/7/2008', 'WHITE', 6 )
INSERT INTO @table2 values('1/8/2008', 'WHITE', 5 )
INSERT INTO @table2 values('1/9/2008', 'WHITE', 2 )
INSERT INTO @table2 values('1/10/2008', 'WHITE', 3 )
INSERT INTO @table2 values('1/7/2008', 'BLUE', 20) 
INSERT INTO @table2 values('1/8/2008', 'BLUE', 11 )
INSERT INTO @table2 values('1/9/2008', 'BLUE', 15 )


SELECT ISNULL(t1.Col1,t2.Col1) AS Col1,
       ISNULL(t1.Col2,t2.Col2) AS Col2,
       t1.Col3,
       t2.Col3 AS Col4
INTO #Final
FROM @table1 t1
FULL OUTER JOIN @table2 t2
ON t1.Col1=t2.Col1
AND t1.Col2=t2.Col2
ORDER BY Col2,Col1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources