Forum Discussion

ManishSChoudhari's avatar
ManishSChoudhari
Copper Contributor
Jan 24, 2024

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

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
 
 
  • 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.

Resources