SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Atif Sheikh / Find data difference between two datasets / Latest Posts

RE: Find data difference between two datasets

niall.baird — Thu, 07 Oct 2010 19:48:59 GMT

This sort of jogged my memory when I read it, and believe it or not, two days later I actually needed to do it. Here's my 'real world' example using table variables.Basically, I have an sql statement that is contained within a table, and a stored proc retrieves it & replaces certain values depending on the value of the export_id. This is the way the system currently operates, and it returns x rows, depending on the export idI have to recreate this "brilliant" concept within a series of stored procs, and wanted to capture the relevant data, then check it to make sure that the data returned is exactly the same. This is what I came up with:[code]DECLARE @nbsql VARCHAR(MAX) , @nSQL NVARCHAR(MAX) , @p_exportid INT , @max INT , @p_Reported TINYINT SET NOCOUNT ONDECLARE @tblExportID TABLE (ExportID INT, IsReported TINYINT);DECLARE @tblExportOrig TABLE (ExportValue VARCHAR(MAX))DECLARE @tblExportNew TABLE (ExportValue VARCHAR(MAX))INSERT INTO @tblExportID SELECT exportid, Isreported FROM dbo.ref_ExportID rei WHERE rei.ExportType = 'SomeValue' AND rei.IsValid = 1SET @p_exportid = (SELECT TOP 1(exportid) FROM @tblExportID)SET @p_Reported = (SELECT TOP 1(exportid) FROM @tblExportID)SET @max = (SELECT COUNT(*) FROM @tblExportID)WHILE @max > 0 BEGIN -- Retrieve the 'dynamic' sql from the table EXECUTE nb_SP_Export_Data_Thread @ProcessDate = '20101001', @exportid = @p_exportid, @nbsql = @nbsql OUTPUT SET @nSql = convert(nvarchar(max),@nbsql) -- Get the original data from the dynamic sql, put it into the table variable INSERT INTO @tblExportOrig EXECUTE SP_executesql @nsql -- Get the data from the new stored proc, put it into the second table variable INSERT INTO @tblExportNew EXECUTE SP_Export_GLExtract @ExportID = @p_exportid, @Reported = @p_Reported, @ProcessDate = '20101001' -- Maintain the control table DELETE FROM @tblExportID WHERE ExportID = @p_exportid SET @max = (SELECT COUNT(*) FROM @tblExportID) SET @p_exportid = (SELECT TOP 1(exportid) FROM @tblExportID) SET @p_Reported = (SELECT TOP 1(exportid) FROM @tblExportID) -- Firstly select the data that matches in the two tables (this should be the same rowcount -- as the original statement will return SELECT 'Match' 'Matching Data', @p_exportid 'Export ID', o.ExportValue 'OrigValue', n.ExportValue 'New Value' FROM @tblExportOrig o INNER JOIN @tblExportNew n ON o.ExportValue = n.ExportValue ORDER BY 1,2; -- Next, SELECT 'In Orig, not in New' 'Orig Table', @p_exportid 'Export ID', o.ExportValue 'OrigValue', n.ExportValue 'New Value' FROM @tblExportOrig o INNER JOIN @tblExportNew n ON o.ExportValue = n.ExportValue WHERE NOT exists (SELECT ExportValue FROM @tblExportNew) ORDER BY 1,2; -- Finally, what have we put into the new table, that never existed in the old table SELECT 'In New, not in Orig' 'New Table', @p_exportid 'Export ID', o.ExportValue 'OrigValue', n.ExportValue 'New Value' FROM @tblExportOrig o INNER JOIN @tblExportNew n ON o.ExportValue = n.ExportValue WHERE NOT EXISTS (SELECT ExportValue FROM @tblExportOrig) ORDER BY 1,2; DELETE FROM @tblExportOrig; DELETE FROM @tblExportNew; END SET NOCOUNT OFF[/code]

RE: Find data difference between two datasets

herbertmungazi — Thu, 07 Oct 2010 06:10:51 GMT

This is a helpful concept...i am actually using this to write an application to check if a student's previous results in certain pre-requisite courses meet the minimum requirements.If the requirements dataset has differences with the student results dataset then they dont meet the pre-requisites...Dude this is a great article!!and i only picked one syntax error and that had to do with a tab.

RE: Find data difference between two datasets

Atif-ullah Sheikh — Mon, 04 Oct 2010 22:16:59 GMT

The script seems to be embeded with special characters. Please remove tab before + name + in the query right after --Data approach comment and then execute.[code="sql"]-- Data approachSelect @vCols = Stuff((Select ',case when a.' + [name] + ' = b.' + [name] + ' then Cast(b.' + [name] + ' as varchar(10)) else cast(b.' + [name] + ' as varchar(max)) + ''(old)'' + '' '' + Cast(a.' + [name] + ' as varchar(10)) + ''(new)'' end as ' + [name]from sys.columns where Object_id = Object_id('vTable1') for XML Path('')),1,1,'')[/code]The comments in the script tells what it performs.Thanks for the feedback and feel sorry for the inconvenience.

RE: Find data difference between two datasets

Jeffery Williams — Mon, 04 Oct 2010 21:09:41 GMT

Does not work unless I re-write it... I just happen to need this exact thing right now... Gives me a couple ideas on how to handle the challenge, thank you... BUT.... Does not work as posted. I will post an update shortly.

RE: Find data difference between two datasets

niall.baird — Mon, 04 Oct 2010 14:55:36 GMT

It would be handy if there were no syntax errors.

RE: Find data difference between two datasets

Douglas Osborne-456728 — Mon, 04 Oct 2010 11:44:40 GMT

What is this script supposed to demonstrate? This seems like a silly post when there is no discussion as to what it accomplishes and why you would use this 'technique.'My .02

RE: Find data difference between two datasets

ssaari — Mon, 04 Oct 2010 10:04:51 GMT

Seems to be rife with syntax errors.

Find data difference between two datasets

Atif-ullah Sheikh — Sat, 02 Oct 2010 18:56:54 GMT

Comments posted to this topic are about the item [B]Find data difference between two datasets[/B]