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
)
Go to Top of Page
   

- Advertisement -