SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Find data difference between two datasets


Find data difference between two datasets

Author
Message
Atif-ullah Sheikh
Atif-ullah Sheikh
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3806 Visits: 5190
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


ssaari
ssaari
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 61
Seems to be rife with syntax errors.
Douglas Osborne-456728
Douglas Osborne-456728
SSC Veteran
SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)

Group: General Forum Members
Points: 208 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
niall.baird
niall.baird
Old Hand
Old Hand (358 reputation)Old Hand (358 reputation)Old Hand (358 reputation)Old Hand (358 reputation)Old Hand (358 reputation)Old Hand (358 reputation)Old Hand (358 reputation)Old Hand (358 reputation)

Group: General Forum Members
Points: 358 Visits: 484
It would be handy if there were no syntax errors.
Jeffery Williams
Jeffery Williams
SSC-Addicted
SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)

Group: General Forum Members
Points: 431 Visits: 913
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
Atif-ullah Sheikh
Atif-ullah Sheikh
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3806 Visits: 5190
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


herbertmungazi
herbertmungazi
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 41
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.
niall.baird
niall.baird
Old Hand
Old Hand (358 reputation)Old Hand (358 reputation)Old Hand (358 reputation)Old Hand (358 reputation)Old Hand (358 reputation)Old Hand (358 reputation)Old Hand (358 reputation)Old Hand (358 reputation)

Group: General Forum Members
Points: 358 Visits: 484
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


Iwas Bornready
Iwas Bornready
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14048 Visits: 885
Thanks for the script.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search