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

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: