Find data difference between two datasets

  • Atif-ullah Sheikh

    SSChampion

    Points: 12495

    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
    [font="Arial Black"]here[/font][/url][/right]

  • ssaari

    SSC Journeyman

    Points: 92

    Seems to be rife with syntax errors.

  • Douglas Osborne-456728

    SSCommitted

    Points: 1569

    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

    Hall of Fame

    Points: 3468

    It would be handy if there were no syntax errors.

  • Jeffery Williams

    SSCertifiable

    Points: 6441

    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

    SSChampion

    Points: 12495

    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
    [font="Arial Black"]here[/font][/url][/right]

  • herbertmungazi

    Valued Member

    Points: 56

    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

    Hall of Fame

    Points: 3468

    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

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    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