csePixelated
asked on
compair for rows that do not exist between SQL tables
i am trying to compare the same database on 2 different servers. I am trying to only display results where one of 6 fields do not match between the tables in question or if an a row does exist on one while it does on the other. it seems to work so far, the problem is that it does not include results for AccountID discrepencies. as in the AccountID (and thus the row) does not exist on one table when it does on the other, how do i handle this?
any help in getting this figured out would be greatly appreciated...
B.Account, B.AcctLineCode, B.AcctNum, B.Name, B.UserField1, B.UserField2, B.UserField3,
01, aa, 1000, bob dole, na, na, yes,
02, aa, 1400, betty dole, na, na, yes,
03, aa, 1050, bob 2, na, na, yes,
04, aa, 4500, bob 3, na, na, yes,
05, aa, 1060, nobody, na, na, yes,
06, aa, 7000, Thomas Coleville, na, na, yes,
07, aa, 8000, the soup shop, na, na,no,
09, aa, 1234, Bruce banner, na, Hulk, na,
10, aa, 1555, steve b, na, na, yes,
11, aa, 1001, boby b, na, na, yes,
A.Account, A.AcctLineCode, A.AcctNum, A.Name, A.UserField1, A.UserField2, A.UserField3,
01, aa, 1000, bob dole, na, na, yes,
03, aa, 1050, bob 2, na, na, yes,
04, aa, 4500, bob 3, na, na, yes,
05, aa, 1060, nobody, na, na, yes,
06, aa, 7000, Thomas Coleville, na, na, yes,
07, aa, 8000, the soup shop, na, na,no,
08, aa, 1230, nathen m, na, na, yes,
09, aa, 1234, Bruce banner, na, NA, na,
I'm looking for my results from comparing the above to look like ( dashes not necessary )
-, 08, -, aa, -, 1230, -, nathen m, -, na, -, na, -, yes,
09, 09, aa, aa, 1234, 1234, Bruce banner, Bruce banner, na, na, Hulk, NA, na, na,
10, -, aa, -, 1555, -, steve b, -, na, -, na, -, yes,
11, -, aa, -, 1001, -, boby b, -, na, -, na, -, yes,
instead of what it does...
09, 09, aa, aa, 1234, 1234, Bruce banner, Bruce banner, na, na, Hulk, NA, na, na,
SELECT A.AccountID AS ACCID,
B.AcctLineCode AS OLC,
B.AcctNum AS OAN,
A.AcctLineCode as NLC,
A.AcctNum AS NAN,
B.Name AS ONA,
A.Name as NNA,
B.UserField1 AS O1,
B.UserField2 AS O2,
B.UserField3 AS O3,
A.UserField1 AS N1,
A.UserField2 AS N2,
A.UserField3 AS N3
FROM [CDISERV1].[SUBSCRIBER].[dbo].[Subscriber Data] A
LEFT OUTER JOIN [CDISERV2].[SUBSCRIBER].[dbo].[Subscriber Data] B ON A.AccountID = B.AccountID
WHERE A.AccountID != B.AccountID
OR A.AcctLineCode != B.AcctLineCode
OR A.AcctNum != B.AcctNum
OR A.Name != B.Name
OR A.UserField1 != B.UserField1
OR A.UserField2 != B.UserField2
OR A.UserField3 != B.UserField3
any help in getting this figured out would be greatly appreciated...
B.Account, B.AcctLineCode, B.AcctNum, B.Name, B.UserField1, B.UserField2, B.UserField3,
01, aa, 1000, bob dole, na, na, yes,
02, aa, 1400, betty dole, na, na, yes,
03, aa, 1050, bob 2, na, na, yes,
04, aa, 4500, bob 3, na, na, yes,
05, aa, 1060, nobody, na, na, yes,
06, aa, 7000, Thomas Coleville, na, na, yes,
07, aa, 8000, the soup shop, na, na,no,
09, aa, 1234, Bruce banner, na, Hulk, na,
10, aa, 1555, steve b, na, na, yes,
11, aa, 1001, boby b, na, na, yes,
A.Account, A.AcctLineCode, A.AcctNum, A.Name, A.UserField1, A.UserField2, A.UserField3,
01, aa, 1000, bob dole, na, na, yes,
03, aa, 1050, bob 2, na, na, yes,
04, aa, 4500, bob 3, na, na, yes,
05, aa, 1060, nobody, na, na, yes,
06, aa, 7000, Thomas Coleville, na, na, yes,
07, aa, 8000, the soup shop, na, na,no,
08, aa, 1230, nathen m, na, na, yes,
09, aa, 1234, Bruce banner, na, NA, na,
I'm looking for my results from comparing the above to look like ( dashes not necessary )
-, 08, -, aa, -, 1230, -, nathen m, -, na, -, na, -, yes,
09, 09, aa, aa, 1234, 1234, Bruce banner, Bruce banner, na, na, Hulk, NA, na, na,
10, -, aa, -, 1555, -, steve b, -, na, -, na, -, yes,
11, -, aa, -, 1001, -, boby b, -, na, -, na, -, yes,
instead of what it does...
09, 09, aa, aa, 1234, 1234, Bruce banner, Bruce banner, na, na, Hulk, NA, na, na,
Just to give you an hint, to begin with, use full outer join. Let me try to put whatever you have given in SSMS and attempt to give query to you in a moment.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You might try using set operators -
Select AccountID,AcctLineCode,Acc tNum,Name, UserField1 ,UserField 2,UserFiel d3
from A
except
Select AccountID,AcctLineCode,Acc tNum,Name, UserField1 ,UserField 2,UserFiel d3
from B
This will give you the records for the selected columns in A that don't exist in B. Reversing the order of the select statements will give you the records for the selected columns in B that don't exist in A.
Select AccountID,AcctLineCode,Acc
from A
except
Select AccountID,AcctLineCode,Acc
from B
This will give you the records for the selected columns in A that don't exist in B. Reversing the order of the select statements will give you the records for the selected columns in B that don't exist in A.
ASKER
Nitin Sontakke, i do appreciate the input.
here is what i have now
here is what i have now
SELECT isnull(A.AccountID, B.AccountID) AccountID
,case when A.AccountID is not null and B.AccountID is not null then 'Found in both'
when A.AccountID is not null and B.AccountID is null then 'Missing in B'
when A.AccountID is null and B.AccountID is not null then 'Missing in A' end [Result],
A.AccountID AS ACCID,
B.AcctLineCode AS OLC,
B.AcctNum AS OAN,
A.AcctLineCode as NLC,
A.AcctNum AS NAN,
B.Name AS ONA,
A.Name as NNA,
B.UserField1 AS O1,
B.UserField2 AS O2,
B.UserField3 AS O3,
A.UserField1 AS N1,
A.UserField2 AS N2,
A.UserField3 AS N3
FROM [CDISERV1].[SUBSCRIBER].[dbo].[Subscriber Data] A
FULL OUTER JOIN [CDISERV2].[SUBSCRIBER].[dbo].[Subscriber Data] B ON A.AccountID = B.AccountID
WHERE A.AcctLineCode != B.AcctLineCode
OR A.AcctNum != B.AcctNum
OR A.Name != B.Name
OR A.UserField1 != B.UserField1
OR A.UserField2 != B.UserField2
OR A.UserField3 != B.UserField3
If i want to return results for all rows in both tables I can, the problem I am facing is that i need only to list the differences in these fields in my results, in that, i can't seem to include the " Missing in A " & " Missing in B" results while only getting "Found in both" if it matches my other criteria (the tables i am comparing have over 10 columns i only need to know if a line exists on one that does not on the other or if specific fields don't match.
ASKER
It needs refining but you gave me my answer, thankyou Nitin Sontakke.
SELECT isnull(A.AccountID, B.AccountID) AccountID
,case when A.AccountID is not null and B.AccountID is not null then 'Found in both'
when A.AccountID is not null and B.AccountID is null then 'ADDED'
when A.AccountID is null and B.AccountID is not null then 'DEELETED' end [Result],
A.AccountID AS ACCID,
B.AcctLineCode AS OLC,
B.AcctNum AS OAN,
A.AcctLineCode as NLC,
A.AcctNum AS NAN,
B.Name AS ONA,
A.Name as NNA,
B.UserField1 AS O1,
B.UserField2 AS O2,
B.UserField3 AS O3,
A.UserField1 AS N1,
A.UserField2 AS N2,
A.UserField3 AS N3
FROM [CDISERV1].[SUBSCRIBER].[dbo].[Subscriber Data] A
FULL OUTER JOIN [CDISERV2].[SUBSCRIBER].[dbo].[Subscriber Data] B ON A.AccountID = B.AccountID
WHERE (A.AccountID is not null and B.AccountID is null)
OR (A.AccountID is null and B.AccountID is not null)
OR A.AcctLineCode != B.AcctLineCode
OR A.AcctNum != B.AcctNum
OR A.Name != B.Name
OR A.UserField1 != B.UserField1
OR A.UserField2 != B.UserField2
OR A.UserField3 != B.UserField3
Might as well formally close the question, if you are happy with what you have got so far.
ASKER
Did not realise i had not closed it, ty again Nitin Sontakke.