Data Compare between two MS SQL Tables

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.

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;  

;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];

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.

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  

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.