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(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
- BabatundeDallasBrass 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!Best Regards,Dallas.