Comparing and Merging two tables

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 2005 Forums

Transact-SQL (2005)

Comparing and Merging two tables

Author

Topic

stamford
Starting Member

47 Posts

Posted - 2014-02-04 : 12:21:46

I have two scripts which produce the following two tables(t1 and t2).
Is there a way of merging the two tables to product table t3?
The first 5 rows of each table have the same value in the first column (Number).
However in t1 the columns PreOpOrg and PreOpModality are populated but in t2 they are NULL, and vice versa for columns PostOpOrg and PostOpModality.
So the merge would populate the columns with the non-NULL values from each table (t1 and t2). For the rows in t1 and t2 that only appear in one table then they should appear in t3 as if a normal UNION were being attempted.
Unfortunately while I am working in SQL2005 the script needs to be compatible with SQL2000.
Many thanks.


--t1
Number	        DateOfBirth	        PreOpOrg	PreOpModality	PostOpOrg	PostOpModality	DiagDate
8643576344	01/04/1935  00:00:00	RBA11	        2	        NULL	        NULL	        04/09/2012 00:00
2345680016	01/01/1960  00:00:00	RBA11	        4	        NULL	        NULL	        11/11/2011 00:00
6955934252	20/01/1980  00:00:00	RBA11	        4	        NULL	        NULL	        22/08/2012 00:00
2234582369	28/12/1940  00:00:00	RBA11	        5	        NULL	        NULL	        02/11/2011 00:00
9477684368	01/02/1959  00:00:00	RBA11	        5	        NULL	        NULL	        02/01/2014 00:00
						
--t2						
Number	        DateOfBirth	        PreOpOrg	PreOpModality	PostOpOrg	PostOpModality	DiagDate
8643576344	01/04/1935  00:00:00	NULL	        NULL	        RBA11	        2	        04/09/2012 00:00
2345680016	01/01/1960  00:00:00	NULL	        NULL	        RBA11	        2	        11/11/2011 00:00
6955934252	20/01/1980  00:00:00	NULL	        NULL	        RN325	        5	        22/08/2012 00:00
2234582369	28/12/1940  00:00:00	NULL	        NULL	        RBA11	        5	        02/11/2011 00:00
9477684368	01/02/1959  00:00:00	NULL	        NULL	        RBA11	        5	        02/01/2014 00:00
0000001564	01/01/1970  00:00:00	NULL	        NULL	        RBA11	        2	        08/08/2012 00:00
4225127842	11/07/1948  00:00:00	NULL	        NULL	        RBA11	        2	        10/09/2011 00:00
0909093842	01/07/1957  00:00:00	NULL	        NULL	        RN325	        5	        26/08/2009 00:00
8528528855	05/12/1969  00:00:00	NULL	        NULL	        RBA11	        5	        25/10/2010 00:00
1000001367	05/05/1950  00:00:00	NULL	        NULL	        RBA11	        5	        08/04/2011 00:00
2234573513	30/11/1946  00:00:00	NULL	        NULL	        RN325	        5	        20/04/2011 00:00
1000001566	18/03/1951  00:00:00	NULL	        NULL	        RBA11	        5	        20/03/2012 00:00
0000001600	01/01/1970  00:00:00	NULL	        NULL	        RBA11	        5	        28/03/2013 00:00
1000001621	12/12/1950  00:00:00	NULL	        NULL	        RBA11	        5	        06/12/2013 00:00
						
--t3						
Number	        DateOfBirth	        PreOpOrg	PreOpModality	PostOpOrg	PostOpModality	DiagDate
8643576344	01/04/1935  00:00:00	RBA11	        2	        RBA11	        2	        04/09/2012 00:00
2345680016	01/01/1960  00:00:00	RBA11	        4	        RBA11	        2	        11/11/2011 00:00
6955934252	20/01/1980  00:00:00	RBA11	        4	        RN325	        5	        22/08/2012 00:00
2234582369	28/12/1940  00:00:00	RBA11	        5	        RBA11	        5	        02/11/2011 00:00
9477684368	01/02/1959  00:00:00	RBA11	        5	        RBA11	        5	        02/01/2014 00:00
0000001564	01/01/1970  00:00:00	NULL	        NULL	        RBA11	        2	        08/08/2012 00:00
4225127842	11/07/1948  00:00:00	NULL	        NULL	        RBA11	        2	        10/09/2011 00:00
0909093842	01/07/1957  00:00:00	NULL	        NULL	        RN325	        5	        26/08/2009 00:00
8528528855	05/12/1969  00:00:00	NULL	        NULL	        RBA11	        5	        25/10/2010 00:00
1000001367	05/05/1950  00:00:00	NULL	        NULL	        RBA11	        5	        08/04/2011 00:00
2234573513	30/11/1946  00:00:00	NULL	        NULL	        RN325	        5	        20/04/2011 00:00
1000001566	18/03/1951  00:00:00	NULL	        NULL	        RBA11	        5	        20/03/2012 00:00
0000001600	01/01/1970  00:00:00	NULL	        NULL	        RBA11	        5	        28/03/2013 00:00
1000001621	12/12/1950  00:00:00	NULL	        NULL	        RBA11	        5	        06/12/2013 00:00

Ifor
Aged Yak Warrior

700 Posts

Posted - 2014-02-05 : 07:40:17

Something like:


SELECT t1.Number, t1.DateOfBirth
	,COALESCE(t1.PreOpOrg, t2.PreOpOrg) AS PreOpOrg
	,COALESCE(t1.PreOpModality, t2.PreOpModality) AS PreOpModality
	,COALESCE(t2.PostOpOrg, t1.PostOpOrg) AS PostOpOrg
	,COALESCE(t2.PostOpModality, t1.PostOpModality) AS PostOpModality
	,t1.DiagDate
FROM t1
	JOIN t2
		ON t1.Number = t2.Number
UNION ALL
SELECT Number, DateOfBirth, PreOpOrg, PreOpModality, PostOpOrg, PostOpModality, DiagDate
FROM t2
WHERE NOT EXISTS
(
	SELECT 1
	FROM t1
	WHERE t1.Number = t2.Number
)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources