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 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 = (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
May 18, 2016 at 7:02 am
Thanks for the script.
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy