SQL Server Script to Generate the Source and Target Compare Script Dynamically and Compare it

Copper Contributor
CREATE PROCEDURE [DBO].[USP_DATA_MISMATCH]
(
@SOURCE_TABLE_NAME VARCHAR(200),
@target_TABLE_NAME VARCHAR(200),
@COMMON_COLUMN VARCHAR(200), 
@SCHEMA_NAME VARCHAR(200),
@INTO_TABLE_NAME VARCHAR(200)
)
/*
AUTHOR : MANISH CHOUDHARI
DESCRIPTION : GENERATE DYNAMIC SQL TO COMPARE TWO DATA SOURCES
THIS STORED PROCEDURE WILL COMPARE THE RECORDS FROM TABLE PRODUCT AND PRODUCT1 FROM AdventureWorks2022 DATABASE
 
==================================================================================================================================================================================
EXEC  DBO.USP_DATA_MISMATCH @SOURCE_TABLE_NAME = 'Product' , @target_TABLE_NAME = 'Product1', @COMMON_COLUMN = 'ProductID' ,@SCHEMA_NAME = 'Production', @INTO_TABLE_NAME = NULL
==================================================================================================================================================================================
 
*/
AS
BEGIN
SET NOCOUNT ON
 
/* 
 
DECLARE @COMMON_COLUMN VARCHAR(200), @INTO_TABLE_NAME VARCHAR(200), @SCHEMA_NAME VARCHAR(200), @SOURCE_TABLE_NAME VARCHAR(200) , @target_TABLE_NAME VARCHAR(200)
SET @COMMON_COLUMN = 'ProductID'
SET @INTO_TABLE_NAME = 'TEMP'
SET @SCHEMA_NAME = 'Production'
SET @SOURCE_TABLE_NAME = 'Product' 
SET @target_TABLE_NAME = 'Product1'
 
*/
DECLARE @DYN_SQL VARCHAR(MAX) = ''
IF OBJECT_ID('DBO.SRC_TRG_COLUMNS') IS NOT NULL DROP TABLE DBO.SRC_TRG_COLUMNS  
 
CREATE TABLE DBO.SRC_TRG_COLUMNS
(
SR_NO INT,
SOURCE_COLUMN VARCHAR(500),
TARGET_COLUMN VARCHAR(500),
COLUMN_DATA_TYPE VARCHAR(100),
COLUMN_LENGTH INT
)
 
INSERT INTO DBO.SRC_TRG_COLUMNS(SR_NO,SOURCE_COLUMN,COLUMN_DATA_TYPE,COLUMN_LENGTH)
SELECT ROW_NUMBER()OVER(ORDER BY C.NAME) AS SR_NO, C.NAME AS [COLUMN_NAME], TYPE_NAME(C.SYSTEM_TYPE_ID) AS COLUMN_DATA_TYPE, C.[MAX_LENGTH] AS COLUMN_LENGTH
FROM SYS.COLUMNS C
INNER JOIN SYS.TABLES T
ON C.OBJECT_ID = T.OBJECT_ID
WHERE T.NAME = @SOURCE_TABLE_NAME
AND SCHEMA_NAME(T.SCHEMA_ID) = @SCHEMA_NAME
 
UPDATE SRC
SET SRC.TARGET_COLUMN = TMPR.COLUMN_NAME
FROM  DBO.SRC_TRG_COLUMNS SRC INNER JOIN 
(
SELECT ROW_NUMBER()OVER(ORDER BY C.NAME) AS SR_NO,C.NAME AS [COLUMN_NAME]
FROM SYS.COLUMNS C
INNER JOIN SYS.TABLES T
ON C.OBJECT_ID = T.OBJECT_ID
WHERE T.NAME = @target_TABLE_NAME
AND SCHEMA_NAME(T.SCHEMA_ID) = @SCHEMA_NAME
)TMPR
ON SRC.SR_NO = TMPR.SR_NO
 
 
SELECT 
@DYN_SQL += [SQL]
FROM
(
SELECT 1 AS SEQ,
'SELECT '
+ 'S.[' +  @COMMON_COLUMN + ']'
+ ', T.[' + @COMMON_COLUMN + '],' AS [SQL]
UNION
SELECT 
2 AS SEQ, CONCAT(
' S.[',TRIM(SOURCE_COLUMN),']', 
', ',  
'T.[',TRIM(TARGET_COLUMN),']',
', ',
'CASE WHEN ',
CASE WHEN UPPER(COLUMN_DATA_TYPE) NOT IN ('DECIMAL','NUMERIC') THEN 
'COALESCE(S.[' + TRIM(SOURCE_COLUMN) + '],'''') = COALESCE(T.[' + TRIM(TARGET_COLUMN) + '],'''') THEN ''YES'' ELSE ''NO'' END'
ELSE
'COALESCE(S.[' + TRIM(SOURCE_COLUMN) + '],0) = COALESCE(T.[' + TRIM(TARGET_COLUMN) + '],0) THEN ''YES'' ELSE ''NO'' END'
END,
' AS ',
TRIM(SOURCE_COLUMN),
'_Match', CASE WHEN SR_NO <> (SELECT MAX(SR_NO) FROM DBO.SRC_TRG_COLUMNS) THEN ',' ELSE '' END   
) AS [SQL]
FROM DBO.SRC_TRG_COLUMNS
UNION
SELECT 3 AS SEQ, CASE WHEN @INTO_TABLE_NAME IS NULL THEN '' ELSE 'INTO ' + @INTO_TABLE_NAME END
UNION 
SELECT 4 AS SEQ, CONCAT(' FROM [', @SCHEMA_NAME,'].[' ,@SOURCE_TABLE_NAME, '] S INNER JOIN [',  @SCHEMA_NAME, '].[', @target_TABLE_NAME, ']  T ON S.', @COMMON_COLUMN , ' = T.' , @COMMON_COLUMN )    
)
TMPR
ORDER BY SEQ
 
--PRINT @DYN_SQL
EXEC @DYN_SQL
END
 
GO
 
 
1 Reply

@ManishSChoudhari, here is the modified script that can be used fo comparison.

 

CREATE PROCEDURE [DBO]. [USP_DATA_MISMATCH] (
    @SOURCE_TABLE_NAME VARCHAR (200),
    @target _TABLE_NAME VARCHAR (200),
    @COMMON_COLUMN VARCHAR (200),
    @SCHEMA_NAME VARCHAR (200),
    @INTO_TABLE_NAME VARCHAR (200)
)
/* AUTHOR : MANISH CHOUDHARI
DESCRIPTION : GENERATE DYNAMIC SQL TO COMPARE TWO DATA SOURCES
THIS STORED PROCEDURE WILL COMPARE THE RECORDS FROM TABLE PRODUCT AND PRODUCT1 FROM AdventureWorks2022 DATABASE
==================================================================================================================================================================================
EXEC DBO.USP_DATA_MISMATCH
@SOURCE_TABLE_NAME = 'Product',
@target _TABLE_NAME = 'Product1',
@COMMON_COLUMN = 'ProductID',
@SCHEMA_NAME = 'Production',
@INTO_TABLE_NAME = NULL
================================================================================================================================================================================== */
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @DYN_SQL VARCHAR (MAX) = ''
    IF OBJECT_ID ('DBO.SRC_TRG_COLUMNS') IS NOT NULL DROP TABLE DBO.SRC_TRG_COLUMNS
    CREATE TABLE DBO.SRC_TRG_COLUMNS (
        SR_NO INT,
        SOURCE_COLUMN VARCHAR (500),
        TARGET_COLUMN VARCHAR (500),
        COLUMN_DATA_TYPE VARCHAR (100),
        COLUMN_LENGTH INT
    )
    INSERT INTO DBO.SRC_TRG_COLUMNS (SR_NO,SOURCE_COLUMN,COLUMN_DATA_TYPE,COLUMN_LENGTH)
    SELECT ROW_NUMBER ()OVER (ORDER BY C.NAME) AS SR_NO, C.NAME AS [COLUMN_NAME], TYPE_NAME (C.SYSTEM_TYPE_ID) AS COLUMN_DATA_TYPE, C. [MAX_LENGTH] AS COLUMN_LENGTH
    FROM SYS.COLUMNS C INNER JOIN SYS.TABLES T ON C.OBJECT_ID = T.OBJECT_ID
    WHERE T.NAME = @SOURCE_TABLE_NAME AND SCHEMA_NAME (T.SCHEMA_ID) = @SCHEMA_NAME
    UPDATE SRC SET SRC.TARGET_COLUMN = TMPR.COLUMN_NAME
    FROM DBO.SRC_TRG_COLUMNS SRC INNER JOIN (
        SELECT ROW_NUMBER ()OVER (ORDER BY C.NAME) AS SR_NO,C.NAME AS [COLUMN_NAME]
        FROM SYS.COLUMNS C INNER JOIN SYS.TABLES T ON C.OBJECT_ID = T.OBJECT_ID
        WHERE T.NAME = @target _TABLE_NAME AND SCHEMA_NAME (T.SCHEMA_ID) = @SCHEMA_NAME
    )TMPR ON SRC.SR_NO = TMPR.SR_NO
    SELECT @DYN_SQL += [SQL] FROM (
        SELECT 1 AS SEQ, 'SELECT ' + 'S. [' + @COMMON_COLUMN + ']' + ', T. [' + @COMMON_COLUMN + '],' AS [SQL]
        UNION
        SELECT 2 AS SEQ, CONCAT (
            ' S. [',TRIM (SOURCE_COLUMN),']', ', ',
            'T. [',TRIM (TARGET_COLUMN),']',","
        ) AS [SQL]
        FROM DBO.SRC_TRG_COLUMNS
        WHERE TARGET_COLUMN IS NOT NULL
        ORDER BY SR_NO
    ) TMP
    SET @DYN_SQL = LEFT (@DYN_SQL, LEN (@DYN_SQL) - 1)
    SET @DYN_SQL += ' FROM ' + @SOURCE_TABLE_NAME + ' S INNER JOIN ' + @target _TABLE_NAME + ' T ON S. [' + @COMMON_COLUMN + '] = T. [' + @COMMON_COLUMN + ']'
    IF @INTO_TABLE_NAME IS NOT NULL
        SET @DYN_SQL = 'SELECT * INTO ' + @INTO_TABLE_NAME + ' FROM (' + @DYN_SQL + ') A'
    EXEC (@DYN_SQL)
END

 

 

The above procedure generates a dynamic SQL statement that compares the records from the source and target tables based on a standard column.

 

 Let me know if this works for you. @ me in replies, or I'll lose your thread!!!  
Note: 

If this post helps, please consider Accepting it as the solution to help others find it more quickly. OR
If my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!
 
Best Regards,
Dallas.