Compare Parallel Data Streams

,

This a script to compare two parallel data streams, in my case production and QA.  Two extracts were created and a mapping table was created as a look-up between old and new recordids.  A final table TestResults was created to hold the results of the comparison.  The comparison table names and join syntax are controlled by variables. 
The script uses syscolumns to find the common column names and compares each row in the two tables, column by column and inserts any differences in to the results table.
The comparisons support nulls and casts any column with the word 'date' in the name as a datetime before conmparing.

declare @icID 		int,
	@iOldID		int,
	@vcTable1	varchar(255),
	@vcTable2	varchar(255),
	@vcColumn 	varchar(255),
	@vcAuditColumn	varchar(255),
	@vcJoin1	varchar(255),
	@vcJoin2	varchar(255),
	@vcQuery  	varchar(2000)


-- table variable to hold the list of common column names
declare @tblCommonColumns  table 	(
	cID 		int identity, 
	ColumnName	varchar(255) 	)

Select 	@vcTable1 	= 'ProdRaw',	--> OriginalData
	@vcTable2	= 'QARaw',		--> New Data
	@vcJoin1 = ' Join dbo.TestMapping tm on t1.RecordID = tm.ProdRecordID and tm.IsProcessed = 0 ', --> added check for processed
	@vcJoin2 = ' t2.RecordID = tm.QARecordID',
	@iOldID	= 0

Insert  @tblCommonColumns
(ColumnName)
-- get the list of all common column names
Select t.name
From	-- First derived table returns all the column names for table 1
(Select 	sc.name
	From 	sysobjects 	so
	join	syscolumns 	sc (nolock) on so.id = sc.id
	where	so.name = @vcTable1 ) t
Join -- Second derived table returns all the column names for table 2
(Select 	sc.name	
	From 	sysobjects 	so
	join	syscolumns 	sc (nolock) on so.id = sc.id
	where	so.name = @vcTable2) t2
			on t.name = t2.name  --> By joining them we get a list of common columns

-- Remove the list of Audit Columns that will never be the same, and other columns that we dont really care about comparing
Delete @tblCommonColumns
Where ColumnName in ('RecordID', 'FileID', 'SourceFileName')


While  1 = 1	-- Do

Begin

-- reset the column Name
	Set @vcColumn = null	
	
-- Get the first Column to work with
	Select 	top 1   @icID	    = cID,
					@vcColumn   = ColumnName
	From @tblCommonColumns
	Where cID > @iOldID
	order by cID

-- check the column name
	If @vcColumn is null
		Break

	Set @vcQuery = 

		'Insert into tempdb.dbo.TestResults ' + 
		'(OldRecordID, NewRecordID, OldValue, NewValue, ColumnName) ' +
		'Select  t1.RecordID, t2.RecordID, t1.' + @vcColumn   + ', t2.' + @vcColumn + ', ''' + @vcColumn  + ''''+
		 ' From ' 	 + @vcTable1    + ' t1  ' +
		 @vcJoin1 +
		 ' Join ' 	 + @vcTable2    + ' t2 on ' + @vcJoin2 +
			' and Isnull(' + 
								Case
									When CharIndex('Date', @vcColumn) > 0 Then 'Cast(t1.' + @vcColumn + ' as Datetime)'
									Else 't1.' + @vcColumn
								End + ', '''') <> IsNull(' + 
								Case
									When CharIndex('Date', @vcColumn) > 0 Then 'Cast(t2.' + @vcColumn + ' as Datetime)'
									Else 't2.' + @vcColumn
								End + ', '''')'
		 
--Print @vcQuery
	Exec (@vcQuery)	


-- set the next seed
	Set @iOldID = @icID

END

Rate

Share

Share

Rate