Data Compare between two MS SQL Tables

asked 2020-09-13T07:51:36.207+00:00
amjee84 1 Reputation point

I have two identical (Schema/Table Structure) Tables in Two Database. DataBase1.SourceTable and DataBase2.TargetTable
Columns on both are :- [ID] INT Identity(1,1), [Name] Varchar(100) NULL, [Description] Varchar(500) NULL, IsActive (bit) Default(0) NOT NULL

I need 3 operations result Combined as below... (To Show Which ID is deleted from source, which id is inserted in source and which ID update in Source, and if Updated in Source, then which column value)

I could possibly try detecting Insert Or Delete, easily using EXCEPT or FULL JOIN etc, and to detect UPDATE I could use CHECKSUM (Though not perfect) but I dont know how to easily detect which column is updated.

Can someone assist me please for these operation of detecting changes? It MUST be via T-SQL/SQL Stored Proc, so please don't recommend PowerShell/TableDiff etc etc. or any 3rd party tool kindly.

24150-image.png

No comments
{count} votes

5 answers

Sort by: Most helpful
  1. answered 2020-09-13T17:19:02.037+00:00
    Erland Sommarskog 67,636 Reputation points Microsoft MVP

    Here is a solution based on FULL JOIN. The purpose of the subqueries with INTERSECT is to compare the columns.

    EXISTS(A INSERSECT B)

    is the same as A = B, but it also works when any of A and B are NULL.

    ; WITH CTE AS (
        SELECT id = isnull(trg.id, src.id),
               action = CASE WHEN trg.id IS NULL THEN 'Insert'
                             WHEN src.id IS NULL THEN 'Delete'
                             ELSE 'Update'
                        END, 
              details = CASE WHEN trg.id IS NOT NULL AND
                                  src.id IS NOT NULL
                             THEN CASE WHEN NOT EXISTS (SELECT trg.description
                                                        INTERSECT
                                                        SELECT src.description) 
                                       THEN 'Description '
                                       ELSE ''
                                  END +
                                  CASE WHEN NOT EXISTS (SELECT trg.name
                                                        INTERSECT
                                                        SELECT src.name) 
                                       THEN 'Name'
                                       ELSE ''
                                  END +
                                  CASE WHEN NOT EXISTS (SELECT trg.IsActive
                                                        INTERSECT
                                                        SELECT src.IsActive) 
                                       THEN 'IsActive '
                                       ELSE ''
                                  END
                       END           
        FROM   sourcetbl src
        FULL   JOIN targettbl trg ON src.id = trg.id
    )
    SELECT id, action, details
    FROM   CTE
    WHERE  action IN ('insert', 'delete') OR
           len(details) > 0
    

    PS Had you posted CREATE TABLE + INSERT with sample data, I would have tested my solution.

    No comments

  2. answered 2020-09-13T17:01:18.387+00:00
    Yitzhak Khabinsky 19,846 Reputation points

    Hi @amjee84 ,

    Please try the following conceptual example. It is leveraging T-SQL MERGE statement.
    And learn how to provide DDL and sample data population while asking a question.

    -- DDL and sample data population, start  
    --Create a target table  
    DECLARE @Products TABLE (  
       ProductID INT PRIMARY KEY,  
       ProductName VARCHAR(100),  
       Rate MONEY  
    );  
      
    INSERT INTO @Products  
    VALUES (1, 'Tea', 10.00),  
       (2, 'Coffee', 20.00),  
       (3, 'Muffin', 30.00),  
       (4, 'Biscuit', 40.00);  
      
    --Create source table  
    DECLARE @UpdatedProducts TABLE (  
       ProductID INT PRIMARY KEY,  
       ProductName VARCHAR(100),  
       Rate MONEY  
    );  
      
    INSERT INTO @UpdatedProducts  
    VALUES   (1, 'Tea', 10.00),  
       (2, 'Coffee', 25.00),  
       (3, 'Muffin', 35.00),  
       (5, 'Pizza', 60.00);  
    -- DDL and sample data population, end  
      
    --SELECT * FROM @Products;  
    --SELECT * FROM @UpdatedProducts;  
      
      
    --MERGE SQL statement  
    --Synchronize the target table with  
    --refreshed data from source table  
    ;MERGE INTO @Products /* WITH (UpdLock, HoldLock) */ AS TARGET    
    USING @UpdatedProducts AS SOURCE   
    ON (TARGET.ProductID = SOURCE.ProductID)   
    -- When rows are matched, update the rows if there is any change  
    -- but only if something needs to be updated!!!  
    WHEN MATCHED AND (TARGET.ProductName <> SOURCE.ProductName   
       OR TARGET.Rate <> SOURCE.Rate) THEN   
       UPDATE SET TARGET.ProductName = SOURCE.ProductName,   
             TARGET.Rate = SOURCE.Rate  
    --When no rows are matched, insert the incoming rows from source  
    --table to target table  
    WHEN NOT MATCHED BY TARGET THEN   
       INSERT (ProductID, ProductName, Rate)   
       VALUES (SOURCE.ProductID, SOURCE.ProductName, SOURCE.Rate)  
    --When there is a row that exists in target table and  
    --same row does not exist in source table  
    --then delete this row from target table  
    WHEN NOT MATCHED BY SOURCE THEN   
       DELETE  
    --$action specifies a column of type nvarchar(10)   
    --in the OUTPUT clause that returns one of three   
    --values for each row: 'INSERT', 'UPDATE', or 'DELETE',   
    --according to the action that was performed on that row  
    OUTPUT $action,   
       DELETED.ProductID AS TargetProductID,   
       DELETED.ProductName AS TargetProductName,   
       DELETED.Rate AS TargetRate,   
       INSERTED.ProductID AS SourceProductID,   
       INSERTED.ProductName AS SourceProductName,   
       INSERTED.Rate AS SourceRate;  
    -- INTO #log (a, dt, what);  
      
    /*  
    We can see the results:  
    - Coffee rate was updated from 20.00 to 25.00,   
    - Muffin rate was updated from 30.00 to 35.00,   
    - Biscuit was deleted   
    - Pizza was inserted.  
    */  
    SELECT * FROM @Products;  
    
    No comments

  3. answered 2020-09-13T19:05:19.527+00:00
    Guoxiong 7,681 Reputation points
    ;WITH CTE_Insert AS (
        SELECT [ID], 'INSERT' AS [ACTION], '' AS [DETAILS]
        FROM SourceTable
        WHERE [ID] NOT IN (SELECT [ID] FROM TargetTable)
    ),
    CTE_Delete AS (
        SELECT [ID], 'DELETE' AS [ACTION], '' AS [DETAILS]
        FROM TargetTable
        WHERE [ID] NOT IN (SELECT [ID] FROM SourceTable)
    ),
    CTE_Update AS (
        SELECT s.[ID], 'UPDATE' AS [ACTION], CONCAT(IIF(s.[Name] <> t.[Name], 'NAME, ', ''), IIF(s.[Description] <> t.[Description], 'DESCRIPTION, ', ''), IIF(s.[IsActive] <> t.[IsActive], 'ISVALID, ', '')) AS [DETAILS]
        FROM SourceTable AS s
        INNER JOIN TargetTable AS t ON s.ID = t.ID AND (s.[Name] <> t.[Name] OR s.[Description] <> t.[Description] OR s.[IsActive] <> t.[IsActive])
    )
    SELECT [ID], [ACTION], [DETAILS] FROM CTE_Insert
    UNION ALL
    SELECT [ID], [ACTION], [DETAILS] FROM CTE_Delete
    UNION ALL
    SELECT [ID], [ACTION], SUBSTRING([DETAILS], 1, LEN([DETAILS]) - 1) AS [DETAILS] FROM CTE_Update
    ORDER BY [ID];
    

  4. answered 2020-09-14T07:48:07.063+00:00
    Anonymous

    I propose this solution where I introduced the NO CHANGES status to highlight also the records not subject to modification.

    -- DDL and data population  
    DECLARE @SOURCETABLE TABLE   
    (  
        ID INT PRIMARY KEY  
        , Name VARCHAR(100)  
        , Description VARCHAR (500)  
        , isValid BIT DEFAULT(0) NOT NULL  
    )  
    --  
    DECLARE @TARGETTABLE TABLE   
    (  
        ID INT PRIMARY KEY  
        , Name VARCHAR(100)  
        , Description VARCHAR (500)  
        , isValid BIT DEFAULT(0) NOT NULL  
    )  
    --      
    INSERT INTO @SOURCETABLE VALUES   
        (1,'TOR','AP DOC MODEL', 1)  
        , (2,'MES','EU NEW FUNCTION', 1)  
        , (3,'SSK','HISTORICAL ALIGNMENT', 0)  
        , (6,'ZQR','DISPATCHED QRM', 1)  
    --      
    INSERT INTO @TARGETTABLE VALUES   
        (1,'TOR','AP DOC MODEL', 1)  
        , (2,'MES','EU NEW PLAN', 0)  
        , (3,'SSK','HISTORICAL ALIGNMENT', 0)  
        , (4,'PMT','MANUAL DISPATCH', 1)  
    -- Solution  
    ;WITH CTE_PRE AS  
    (  
        SELECT   
            S.ID AS SOURCE_ID  
            , S.Name AS SOURCE_NAME  
            , S.Description AS SOURCE_DESCRIPTION  
            , S.isValid AS SOURCE_ISVALID  
            , T.ID AS TARGET_ID  
            , T.Name AS TARGET_NAME  
            , T.Description AS TARGET_DESCRIPTION  
            , T.isValid AS TARGET_ISVALID  
            ,  
            (  
                CASE  
                    WHEN S.ID IS NULL THEN ''  
                    WHEN T.ID IS NULL THEN ''  
                ELSE  
                    CASE WHEN (S.Name=T.Name OR (S.Name IS NULL AND T.Name IS NULL)) THEN '' ELSE 'Name,' END  
                    + CASE WHEN (S.Description=T.Description OR (S.Description IS NULL AND T.Description IS NULL)) THEN '' ELSE 'Description,' END  
                    + CASE WHEN S.isValid=T.isValid THEN '' ELSE 'isValid,' END  
                END  
            ) AS DETAILS  
        FROM @SOURCETABLE S  
        FULL JOIN @TARGETTABLE T ON  
            T.ID = S.ID  
    )  
    ,CTE AS  
    (  
        SELECT  
        *  
        ,  
        (  
            CASE  
                WHEN SOURCE_ID IS NULL THEN 'DELETE'  
                WHEN TARGET_ID IS NULL THEN 'INSERT'  
                WHEN LEN(DETAILS) = 0 THEN 'NO CHANGES'  
                ELSE 'UPDATE'  
            END  
        ) AS ACTION  
        FROM CTE_PRE  
    )  
    SELECT  
        COALESCE(SOURCE_ID, TARGET_ID) AS ID  
        , ACTION  
        , (CASE WHEN LEN(DETAILS)>0 THEN LEFT(DETAILS,LEN(DETAILS)-1) ELSE '' END) AS DETAILS  
    FROM CTE  
    WHERE (LEN(DETAILS)>0 OR ACTION <> 'NO CHANGES')  
    ORDER BY ID  
    

    Obviously, just change the last WHERE clause to display only the desired states.

    No comments

  5. answered 2020-09-14T11:44:02.51+00:00
    EchoLiu-MSFT 14,416 Reputation points

    Hi @amjee84 ,

    I tested ErlandSommarskog’s method , I think this is a good method.It only needs slight modifications to work well:

     DECLARE @SOURCETABLE TABLE   
         (  
             ID INT PRIMARY KEY  
             , Name VARCHAR(100)  
             , Description VARCHAR (500)  
             , isValid BIT DEFAULT(0) NOT NULL  
         )  
          
         DECLARE @TARGETTABLE TABLE   
         (  
             ID INT PRIMARY KEY  
             , Name VARCHAR(100)  
             , Description VARCHAR (500)  
             , isValid BIT DEFAULT(0) NOT NULL  
         )  
            
         INSERT INTO @SOURCETABLE VALUES   
             (1,'TOR','AP DOC MODEL', 1)  
             , (2,'MES','EU NEW FUNCTION', 1)  
             , (3,'SSK','HISTORICAL ALIGNMENT', 0)  
             , (6,'ZQR','DISPATCHED QRM', 1)  
             
         INSERT INTO @TARGETTABLE VALUES   
             (1,'TOR','AP DOC MODEL', 1)  
             , (2,'MES','EU NEW PLAN', 0)  
             , (3,'SSK','HISTORICAL ALIGNMENT', 0)  
             , (4,'PMT','MANUAL DISPATCH', 1)  
          
     ; WITH CTE AS (  
         SELECT  isnull(trg.id, src.id) id,  
                 CASE WHEN trg.id IS NULL THEN 'Delete'  
                              WHEN src.id IS NULL THEN 'Insert'  
                              ELSE 'Update'  
                         END action,   
                CASE WHEN trg.id IS NOT NULL AND  
                                   src.id IS NOT NULL  
                              THEN CASE WHEN NOT EXISTS (SELECT trg.description  
                                                         INTERSECT  
                                                         SELECT src.description)   
                                        THEN 'Description'  
                                        ELSE ''  
                                   END +  
                                   CASE WHEN NOT EXISTS (SELECT trg.name  
                                                         INTERSECT  
                                                         SELECT src.name)   
                                        THEN 'Name'  
                                        ELSE ''  
                                   END +  
                                   CASE WHEN NOT EXISTS (SELECT trg.isValid  
                                                         INTERSECT  
                                                         SELECT src.isValid)   
                                        THEN 'isValid'  
                                        ELSE ''  
                                   END  
                        END details            
         FROM   @SOURCETABLE src  
         FULL   JOIN @TARGETTABLE trg ON src.id = trg.id  
     )  
     SELECT id, action, details  
     FROM   CTE  
     WHERE  action IN ('insert', 'delete') OR  
            len(details) > 0  
    

    24466-image.png

    Best Regards
    Echo


    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.