Checking values in tables

Mike 341 Reputation points
2020-11-30T04:36:02.503+00:00

Hello, I have to compare tabl3 #A and #B. Icode is the key and Icode could be many child records. Just want to make sure both tables has same records of each Icode. If any column has mismatch values or missing record(s) in any table then I want in comments to know.

Drop table #A,#B
create table #A (Icode char(10), Qid int, Qcode char(2), Ifname char(10), ILname char(10))
Insert into #A values ('1929',10,'A','Smith','Sam')
Insert into #A values ('1929',11,'B','Jane','Alam')

Insert into #A values ('1930',11,'B','Filip','John')
Insert into #A values ('1930',12,'A','Sam','John')
Insert into #A values ('1930',13,'C','Johni','John')

Insert into #A values ('1931',11,'A','Jimmi','Khan')
Insert into #A values ('1931',12,'B','Marci','Khan')

create table #B (Icode char(10), Qid int, Qcode char(2), Ifname char(10), ILname char(10))
Insert into #B values ('1929',10,'A','Smith','Sam')
Insert into #B values ('1929',11,'B','Jane','Alam')

Insert into #B values ('1930',11,'B','Filip','John')
Insert into #B values ('1930',13,'A','Sam','John')
Insert into #B values ('1930',12,'C','Johni','John')

Insert into #B values ('1931',11,'A','Jimmi','Khan')

/Expected Result/

Select * from #A

Icode Qid Qcode Ifname ILname Comments
1929 10 A Smith Sam All records are same
1929 11 B Jane Alam All records are same

1930 11 B Filip John Qid is not same
1930 12 A Sam John Qid is not same
1930 13 C Johni John Qid is not same

1931 11 A Jimmi Khan
1931 12 B Marci Khan This is missing in table #B

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,552 questions
0 comments
Accepted answer
  1. MelissaMa-MSFT 24,176 Reputation points
    2020-12-01T01:07:53.333+00:00

    Hi @Mike ,

    You could use full join instead of left join in your case after adding one row in #B.

    Please refer the updated one from below and check whether it is helpful to you. 

    Select isnull(a.Icode,b.Icode) Icode,isnull(a.Qid,b.Qid) Qid,  
isnull(a.Qcode,b.Qcode) Qcode,isnull(a.Ifname,b.Ifname) Ifname,  
isnull(a.ILname,b.ILname) ILname,  
case when a.Qid=b.Qid and a.Qcode=b.Qcode then 'All records are same'  
when a.Qid<>b.Qid and a.Qcode=b.Qcode then 'Qid is not same'  
when a.Qid=b.Qid and a.Qcode<>b.Qcode then 'Qcode is not same'  
when b.Icode is null then 'This is missing in table #B'  
when a.Icode is null then 'This is missing in table #A'  
end Comments  
from #A a  
full join #B b  
on a.Icode=b.Icode and a.Ifname=b.Ifname and a.ILname=b.ILname  
order by isnull(a.Icode,b.Icode),isnull(a.Qid,b.Qid)

    Output: 

    Icode	Qid	Qcode	Ifname	ILname	Comments  
1929      	10	A 	Smith     	Sam       	All records are same  
1929      	11	B 	Jane      	Alam      	All records are same  
1930      	11	B 	Filip     	John      	All records are same  
1930      	12	A 	Sam       	John      	Qid is not same  
1930      	13	C 	Johni     	John      	Qid is not same  
1931      	11	A 	Jimmi     	Khan      	All records are same  
1931      	12	B 	Marci     	Khan      	This is missing in table #B  
1939      	12	A 	John      	K         	This is missing in table #A

    Best regards
    Melissa

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    Hot issues November--What can I do if my transaction log is full?
    Hot issues November--How to convert Profiler trace into a SQL Server table

    1 person found this answer helpful.

5 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Sharon Zhao-MSFT 25,056 Reputation points Microsoft Vendor
    2020-11-30T08:23:47.557+00:00

    @Mike ,

    As we are mainly responsible for general issue of Microsoft Teams. Your question which is involved in SQL Server is not included in Teams, I would remove the office-teams-windows-itpro tag and add the sql-server-transact-sql tag. Hope you get better response.

    If the response is helpful, please click "Accept Answer" and upvote it.

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    1 person found this answer helpful.
    0 comments
  2. MelissaMa-MSFT 24,176 Reputation points
    2020-11-30T08:40:10.813+00:00

    Hi @Mike ,

    Please refer below: 

    select A.*,  
case when a.Qid=b.Qid and a.Qcode=b.Qcode  then 'All records are same'   
when a.Qid<>b.Qid and a.Qcode=b.Qcode then 'Qid is not same'  
when a.Qid=b.Qid and a.Qcode<>b.Qcode then 'Qcode is not same'  
when b.Icode is null then  'This is missing in table #B' end Comments  
from #A a   
left join #B b  
on a.Icode=b.Icode  and a.Ifname=b.Ifname and a.ILname=b.ILname  
order by a.Icode,a.Qid

    Output: 

    Icode Qid Qcode Ifname ILname Comments  
1929       10 A  Smith      Sam        All records are same  
1929       11 B  Jane       Alam       All records are same  
1930       11 B  Filip      John       All records are same  
1930       12 A  Sam        John       Qid is not same  
1930       13 C  Johni      John       Qid is not same  
1931       11 A  Jimmi      Khan       All records are same  
1931       12 B  Marci      Khan       This is missing in table #B

    Best regards
    Melissa

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    Hot issues November--What can I do if my transaction log is full?
    Hot issues November--How to convert Profiler trace into a SQL Server table

    0 comments
  3. Mike 341 Reputation points
    2020-11-30T17:59:52.487+00:00

    Hi,
    I have added the following row in #B but not in #A but I don't see the comments i.e. 'This is missing in table #A' from query.

    Insert into #B values ('1939',12,'A','John','K')

    Select A.*,
    case when a.Qid=b.Qid and a.Qcode=b.Qcode then 'All records are same'
    when a.Qid<>b.Qid and a.Qcode=b.Qcode then 'Qid is not same'
    when a.Qid=b.Qid and a.Qcode<>b.Qcode then 'Qcode is not same'
    when b.Icode is null then 'This is missing in table #B'
    when a.Icode is null then 'This is missing in table #A'
    end Comments
    from #A a
    left join #B b
    on a.Icode=b.Icode and a.Ifname=b.Ifname and a.ILname=b.ILname
    order by a.Icode,a.Qid

    0 comments
  4. Yitzhak Khabinsky 24,946 Reputation points
    2020-11-30T18:16:51.587+00:00

    It is better to use set based operators EXCEPT and INTERSECT for such comparisons.
    Check it out below.

    SQL 

    -- DDL and sample data population, start
DECLARE @tbl_a table (Icode char(10), Qid int, Qcode char(2), Ifname char(10), ILname char(10))
Insert into @tbl_a values 
('1929',10,'A','Smith','Sam'),
('1929',11,'B','Jane','Alam'),
('1930',11,'B','Filip','John'),
('1930',12,'A','Sam','John'),
('1930',13,'C','Johni','John'),
('1931',11,'A','Jimmi','Khan'),
('1931',12,'B','Marci','Khan');


DECLARE @tbl_b TABLE (Icode char(10), Qid int, Qcode char(2), Ifname char(10), ILname char(10))
Insert into @tbl_b values 
('1929',10,'A','Smith','Sam'),
('1929',11,'B','Jane','Alam'),
('1930',11,'B','Filip','John'),
('1930',13,'A','Sam','John'),
('1930',12,'C','Johni','John'),
('1931',11,'A','Jimmi','Khan');
-- DDL and sample data population, end

SELECT *, 'tbl_a different row' FROM @tbl_a
EXCEPT
SELECT *, 'tbl_a different row' FROM @tbl_b;

SELECT *, 'identical rows' FROM @tbl_a
INTERSECT
SELECT *, 'identical rows' FROM @tbl_b;

SELECT *, 'tbl_b different row' FROM @tbl_b
EXCEPT
SELECT *, 'tbl_b different row' FROM @tbl_a;
    0 comments