October 2, 2010 at 6:56 pm
Comments posted to this topic are about the item Find data difference between two datasets
October 4, 2010 at 10:04 am
Seems to be rife with syntax errors.
October 4, 2010 at 11:44 am
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
October 4, 2010 at 2:55 pm
It would be handy if there were no syntax errors.
October 4, 2010 at 9:09 pm
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.
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
October 4, 2010 at 10:17 pm
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.
-- Data approach
Select @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,'')
The comments in the script tells what it performs.
Thanks for the feedback and feel sorry for the inconvenience.
October 7, 2010 at 6:10 am
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.
October 7, 2010 at 7:48 pm
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 id
I 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:
DECLARE @nbsql VARCHAR(MAX)
, @nSQL NVARCHAR(MAX)
, @p_exportid INT
, @max-2 INT
, @p_Reported TINYINT
SET NOCOUNT ON
DECLARE @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 = 1
SET @p_exportid = (SELECT TOP 1(exportid) FROM @tblExportID)
SET @p_Reported = (SELECT TOP 1(exportid) FROM @tblExportID)
SET @max-2 = (SELECT COUNT(*) FROM @tblExportID)
WHILE @max-2 > 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-2 = (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
May 18, 2016 at 7:02 am
Thanks for the script.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply