Forum Discussion
ManishSChoudhari
Jan 24, 2024Copper Contributor
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(2...
BabatundeDallas
Jan 24, 2024Brass Contributor
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!
If my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!
Best Regards,
Dallas.