Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Find data difference between two datasets Expand / Collapse
Author
Message
Posted Saturday, October 2, 2010 6:56 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, June 27, 2014 8:02 AM
Points: 3,241, Visits: 4,996
Comments posted to this topic are about the item Find data difference between two datasets

----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Sometimes, winning is not an issue but trying.

You can check my BLOG here

Post #997258
Posted Monday, October 4, 2010 10:04 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, June 15, 2012 2:49 PM
Points: 10, Visits: 61
Seems to be rife with syntax errors.
Post #997675
Posted Monday, October 4, 2010 11:44 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, January 26, 2011 1:32 PM
Points: 146, Visits: 327
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
Post #997746
Posted Monday, October 4, 2010 2:55 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 4:04 PM
Points: 204, Visits: 469
It would be handy if there were no syntax errors.
Post #997902
Posted Monday, October 4, 2010 9:09 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, April 11, 2014 12:48 PM
Points: 139, Visits: 601
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
Post #998045
Posted Monday, October 4, 2010 10:16 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, June 27, 2014 8:02 AM
Points: 3,241, Visits: 4,996
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.


----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Sometimes, winning is not an issue but trying.

You can check my BLOG here

Post #998069
Posted Thursday, October 7, 2010 6:10 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, October 23, 2013 4:20 AM
Points: 18, Visits: 37
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.
Post #1000346
Posted Thursday, October 7, 2010 7:48 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 4:04 PM
Points: 204, Visits: 469
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

Post #1000983
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse