none
How to track the difference data column while comparing with 2 tables having same column names RRS feed

  • Question

  • I have 2 tables Snapshot table and temptable. temptable is holding copy of snapshot table before snapshot gets incremental load. My objective is to pull the Rowid & column and value which ever has difference compare to each other.

    So I need Some code or the ways or articles to implement this.

    Snapshot Table:

    RowIDObject Column2 Column3
    1            Jack   USA
    2            Ryan   GER

    Temp Table:

    RowIDObject Column2 Column3
    1            Jack   USA
    2            Ryan   GER

    Snapshot Table After Incremental load

    RowIDObject Column2 Column3
    1            Jack   UAE
    2            Ryan1   GER

    I want to hold in a new table

    RowID ColumnChanged ActualValue Updated Value
    1       Column3       USA         UAE
    2       Column2       Ryan        Ryan1

    Note: I am using Azure SQL DB.

    Wednesday, August 28, 2019 9:17 AM

All replies

  • select #t1.RowIDObject,
    case 
    when #t1.Column3<>#t2.Column3  then  'Column3' 
    when #t1.Column2<>#t2.Column2  then  'Column2' 
     end colchnaged ,
    case 
    when #t1.Column3<>#t2.Column3  then  #t1.Column3 
    when #t1.Column2<>#t2.Column2  then  #t1.Column2 
     end actval,
     case 
    when #t1.Column3<>#t2.Column3  then  #t2.Column3 
    when #t1.Column2<>#t2.Column2  then  #t2.Column2 
     end updval

     from #t1 join #t2 on #t1.RowIDObject=#t2.RowIDObject

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, August 28, 2019 10:27 AM
    Answerer
  • Thanks Uri, Thanks for your idea. I will try to implement this and share yout the results.
    Wednesday, August 28, 2019 10:35 AM
  • Hi Teja Yarlagadda,

    Your issue is related to Azure SQL database. I suggest you open a thread in Azure SQL database forums, People there will help you more effectively. 
    Hope this could help you.

    Best regards,
    Cathy

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, August 29, 2019 7:12 AM
  • Hi Uri,

    With this idea, i am able to store only one changed column value in the ColChanged . I cannot store more than one column.

    For example Col1 and Col2 has changed its values, i can just store only col1 in colchanged.

    Can you help me here

    Thanks

    Teja

    Wednesday, September 4, 2019 6:06 AM
  • HI Cathy,

    I a have opened a thread in Azure SQL Db.

    Thanks

    Teja

    Wednesday, September 4, 2019 6:55 AM
  • Please post sample data + desired result. Always state what version you are using.
    Please share us more detailed information about your requirement like your table structure  and your expected result and 
    some sample data. So that it will help us understand your issue more clearly. 

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, September 4, 2019 7:07 AM
    Answerer
  • HI URI,

    Thanks for the response,

    Please find example below

    Snapshot Table:

    RowIDObject Column2 Column3
    1            Jack   USA
    2            Ryan   GER

    Temp Table:

    RowIDObject Column2 Column3
    1            Jack   USA
    2            Ryan   GER

    Snapshot Table After Incremental load

    RowIDObject Column2 Column3
    1            Jack   UAE
    2            Ryan1  ITA

    I want to hold in a new table

    RowID ColumnChanged       ActualValue   Updated Value
    1       Column3            USA             UAE
    2       Column2,Column3    Ryan, GER       Ryan1,ITA

    • Proposed as answer by Marios Pavlidis Wednesday, September 4, 2019 7:39 AM
    • Unproposed as answer by Marios Pavlidis Wednesday, September 4, 2019 7:39 AM
    Wednesday, September 4, 2019 7:17 AM
  • Please post CREATE TABLE + INSERT INTO statements

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, September 4, 2019 7:36 AM
    Answerer
  • Hi,

    In case you have a lot of columns and multiple changes per line you would end with a script looping through all columns and this could get tricky. Since both tables have same columns and data types perhaps this could help you:

     with changes as (select * from #t1  except   select * from #t2)
     select *
     from changes c join #t2 t on c.RowIDObject=t.RowIDObject

    My#t1 is your snapshot and my t2 is your temp

    You get only changed rows and both values.

    Wednesday, September 4, 2019 7:45 AM
  • Hi URI,

    Please find the create and insert statements

    create table [snp1](Rowid int, [Name] varchar(50),Country varchar(50))
    create table [SNP2] (Rowid int, [Name] varchar(50),Country varchar(50))

    insert into snp1(Rowid,[Name],Country) values(1,'jack','usa')
    insert into snp1(Rowid,[Name],Country) values(2,'ryan','ger')

    insert into snp2(Rowid,[Name],Country) values(1,'jack','usa')
    insert into SNP2(Rowid,[Name],Country) values(2,'ryanson','ITA')

    looking for an output by comparing both Snp1(incremental table) and SNP2(it is a temp table)

    RowID ColumnChanged       ActualValue   Updated Value
    1       Column3            USA             UAE
    2       Column2,Column3    Ryan, GER       Ryan1,ITA

    Wednesday, September 4, 2019 8:35 AM
  • HI Marios,

    Thanks for your response.

    I am looking for only changed columns and old and new values while comparing 2 similar tables

    Wednesday, September 4, 2019 8:36 AM
  • Hi Teja,

    Have a look on the following code. It checks changes per column and keeps all changes as row records in a temp table. It then groups the information per rowid.

    Needs variables set (table 1, table 2, common column e.g. Rowid)

    Of course the two tables must be identical (same column names and types)

    DECLARE @TABLE1 NVARCHAR(50) = 'SNP1'; DECLARE @TABLE2 NVARCHAR(50) = 'SNP2'; DECLARE @COMMONCOLUMN VARCHAR(50) ='ROWID'; DECLARE @COLNAME NVARCHAR (50); DECLARE @SQL NVARCHAR(1000) DECLARE COLS CURSOR FOR SELECT NAME FROM SYS.COLUMNS WHERE OBJECT_ID=OBJECT_ID(@TABLE1) AND NAME <>@COMMONCOLUMN; BEGIN IF OBJECT_ID('TEMPDB..#CHANGES') IS NOT NULL DROP TABLE #CHANGES; CREATE TABLE #CHANGES (ROWID INT, CC VARCHAR(MAX),OV VARCHAR (MAX),UV VARCHAR(MAX)) OPEN COLS FETCH NEXT FROM COLS INTO @COLNAME WHILE @@FETCH_STATUS = 0 BEGIN SET @SQL= 'INSERT INTO #CHANGES SELECT T1.'+@COMMONCOLUMN+','+ ''''+@COLNAME+''''+',T1.'+@COLNAME+','+'T2.'+@COLNAME+' FROM '+ @TABLE1 + ' T1 JOIN '+ @TABLE2+' T2 ON T1.'+@COMMONCOLUMN+'=T2.'+@COMMONCOLUMN + ' WHERE T1.'+@COLNAME+'<>T2.'+@COLNAME; EXEC SP_EXECUTESQL @SQL; FETCH NEXT FROM COLS INTO @COLNAME END CLOSE COLS DEALLOCATE COLS SELECT ROWID, [CHANGED COLUMNS] = STUFF((SELECT ',' + CC FROM #CHANGES C1 WHERE C1.ROWID=C2.ROWID FOR XML PATH ('')), 1, 1, '') ,[ORIGINAL VALUES]=STUFF((SELECT ',' + OV FROM #CHANGES C1 WHERE C1.ROWID=C2.ROWID FOR XML PATH ('')), 1, 1, '') ,[UPDATED VALUES]=STUFF((SELECT ',' + UV FROM #CHANGES C1 WHERE C1.ROWID=C2.ROWID FOR XML PATH ('')), 1, 1, '') FROM #CHANGES C2 GROUP BY ROWID END

    ---drop table #CHANGES

    Best regards,

    Wednesday, September 4, 2019 1:38 PM
  • Marios, a simple <> compare isn't going to handle nulls properly.

    OP, everyone wants to do this these days, and there are various ways to code it, but as the tables get large it can be hideously slow to run.  The alternative is to use triggers or CDC or even hard-coding to log critical changes as they occur.  This is not free, either, but it can be much more efficient.

    Josh

    Wednesday, September 4, 2019 6:09 PM
  • Hi Josh,

    I totally agree with you; on large tables this will be slow and considering other options as triggers or CDC are way more effective, but for small tables this apporach could work.

    You are also right about the comparison and null behaviour and thus I propose to change the dynamic sql to:

    SET @SQL= 'INSERT INTO #CHANGES SELECT T1.'+@COMMONCOLUMN+','+ ''''+@COLNAME+''''+',T1.'+@COLNAME+','+'T2.'+@COLNAME+'  FROM '+ @TABLE1 + ' T1 JOIN '+ @TABLE2+' T2 ON T1.'+@COMMONCOLUMN+'=T2.'+@COMMONCOLUMN +	' WHERE ISNULL(T1.'+@COLNAME+',''-1'') <>ISNULL(T2.'+@COLNAME+',''-1'') OR COALESCE(T1.'+@COLNAME+',T2.'+@COLNAME+') IS NULL';

    Best regards,

    Marios

    Thursday, September 5, 2019 5:49 AM
  • Thank you guru's for providing me valuable suggestions.

    I tried the fallowing approach and it works for me.

    select t.rowidobject, isnull(a.[col1],'')+' '+isnull(a.[col2],'') as colchanged

    (select rowidobject,
    case When a.[Col1] <> b.[col1] then 'col1' else '' end as [col1],
    case when a.[col2] <> b.[col2] then 'col2' else '' end as col2,

    from snp1 a inner join snp2 b on a.rowidobject=b.rowidobject) t

    Output:

    rowidobject colchanged  

    1                 col3

    2                 col2   col3

    But if there 10 columns i there is change in 1 and 4th column my output was col1 (4 spaces)   col4.
    i want to remove the spaces and put coma between the columns.

    Is there some thing can help me

    Thursday, September 5, 2019 12:02 PM