column level differences SQL

Question

Hi Friends ,
Need help in My requirements implementation .

I need to implement the column level difference, (not a set based operation) need to compare the 2 tables columns and show the result set .

T1
c1 c2 c3

T2
c1 c2 c3

need to compare .....T1.c1=t2.c1

please help how this can be implemted .please share any reference links

Answer 1

Which columns do you want to compare the differences between? Please provide some sample data.

Perhaps the NULLIF function is useful for you:

SELECT *,NULLIF (T1.c2,T2.c2) c2diff,NULLIF (T1.c3,T2.c3) c3diff  
FROM T1  
JOIN T2 ON T1.c1=T2.c1  
 

For details, please refer to:
NULLIF (Transact-SQL)

If you have any question, please feel free to let me know.

Regards
Echo

---

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

Answer 2

That's to less on information.

Please post table design as DDL, some sample data as DML statement and the expected result.

Answer 3

more description :-

I do have Bank Branch DB with table as Payment_tbl which is being compared with the Main Branch DB .with same table name

branch.payment_tbl is being compared with MainBranch.payment_tbl

both the tables are having equal number of columns as well data is also equal as being filtered on the basis of branchid as well accountid .

now ..,
nullif( branch.payment_tbl.seq_nbr,'') <> nullif(mainbranch.payment_tbl.seq_nbr)

this way ...columns are being compared right now . count of columns varied from 20/40 columns

this is a set based operation . i dont want to go for cursor based operation .

is there any other way in SQL to implement this comparison ... .?

Answer 4

Please try:

    CREATE TABLE #diff(c1 datetype,c2 datetype,c3 datetype,  
    diff2 datetype,diff3 datetype)  
       
      
    DECLARE @i INT  
      
    SET @i=1  
      
    WHILE @i<=(SELECT COUNT(c1) FROM T1)  
    BEGIN   
      IF  (SELECT NULLIF (T.c2,T2.c2)  
      FROM (SELECT *,ROW_NUMBER() OVER(ORDER BY c1) rr FROM T1) T  
      JOIN T2 ON T.c1=T2.c1  
      WHERE rr=@i) IS NOT NULL  
      
      INSERT INTO #diff   
      SELECT T.c1,T.c2,T.c3,NULLIF (T.c2,T2.c2) c2diff,NULLIF(T.c3,T2.c3) c3diff  
      FROM (SELECT *,ROW_NUMBER() OVER(ORDER BY c1) rr FROM T1) T  
      JOIN T2 ON T.c1=T2.c1  
      WHERE rr=@i  
      
      SET @i=@i+1  
      
    END  

If you have any question, please feel free to let me know.

Regards
Echo

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".