none
How to track the difference in column values 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  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
    Wednesday, September 4, 2019 6:39 AM

All replies

  • Hi Teja,

    The following MSSQLTips.com blog post discusses this exact scenario. Please see: Ways to compare and find differences for SQL Server tables and data (link).

    The specific example that would be applicable is:

    SELECT *
    FROM      dbtest02.dbo.article d2
    LEFT JOIN dbtest01.dbo.article d1 ON d2.id = d1.id

    This would need to be altered to reflect your snapshot and temp tables where the above is a comparison between two tables in separate DBs hosted on the same SQL Server. The blog is more applicable to SQL Server but does have examples that could be leveraged with Azure SQL Database. 

    Please let us know if you have additional questions.

    Regards,

    Mike

    Wednesday, September 4, 2019 10:21 PM
    Moderator
  • Hi Teja

    Please find the below approach, Let me know if you are looking specifically for any other approaches.

    Using Temporal tables - Ref Link 

    CREATE TABLE WebsiteUserInfo 
    (  
        [UserID] int NOT NULL PRIMARY KEY CLUSTERED 
      , [UserName] nvarchar(100) NOT NULL
      , [PagesVisited] int NOT NULL 
      , [ValidFrom] datetime2 (0) GENERATED ALWAYS AS ROW START
      , [ValidTo] datetime2 (0) GENERATED ALWAYS AS ROW END
      , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
     )  
     WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.WebsiteUserInfoHistory));
    
    
    SELECT * FROM WebsiteUserInfo
    
    SELECT * FROM WebsiteUserInfoHistory
    
    INSERT INTO WebsiteUserInfo ([UserID],[UserName],[PagesVisited])
    VALUES (1,'Test',10)
    
    UPDATE WebsiteUserInfo
    SET PagesVisited = 12
    
    SELECT * FROM WebsiteUserInfo
    
    SELECT * FROM WebsiteUserInfoHistory

    At the end, you should be able to see the History table holding the history of updated content which can be used to perform detail analysis on the changes happened. (Similar result can be attained even with Magic tables, however the mentioned way is one among the latest feature)


    Thanks Naveen Thummlapenta MCSE

    Thursday, September 5, 2019 8:01 AM
  • Hi

    I am writing to follow up this thread with you. Have you solved your issue? If you have solved your issue, please  mark helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue. 

    Thank you


    If this post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    Thursday, September 12, 2019 4:36 AM
  • Hi

    I am writing to follow up this thread with you. Have you solved your issue? If you have solved your issue, please  mark helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue.

    Thank you


    If this post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    Tuesday, October 22, 2019 3:24 AM