how to tell if 2 resultsets are identical

Question

how to tell if 2 resultsets are identical

Hall of Fame

Points: 3679

January 20, 2004 at 5:44 am

Hi,

If I have  2 different queries that return a lot of rows,

Is there any way that I can use T-SQL to return a

TRUE/FALSE value , which indicates whether the

2 result sets are identical?

This is just a guess, maybe it's not possible, or

there's another way that I'm not aware of yet.

Thanks folks.

yogiberr

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

Have you already searched this site?

IIRC, there is a script somewhere here, that compares data. Not sure if in tables and/or resultsets.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

NPeeters SSChampion Points: 12554 More actions · Answer 2

Have you checked the use of CHECKSUM_AGG?

With it, you can calculate a checksum over a complete table or a column. Don't know if it is possible to calculate one over a recordset...

Obviously, if the Checksum is the same, your recordsets are identical.

yogiberr Hall of Fame Points: 3679 More actions · Answer 3

Hi Frank,

yes,

I got hold of a script at:

http://www.sqlservercentral.com/scripts/contributions/458.asp

it compares 2 tables.

So, now I have to convert resultsets into tables.

I will of course be comparing different tables,

eg

tblOne vs tblTwo

tblFive vs tblSix

So, now I suppose my sprocs should return TABLES, which I would assign to variables, then pass them to the "compare" sproc?

Is this the way that most people implement this solution?

cheers,

yogi.

yogiberr Hall of Fame Points: 3679 More actions · Answer 4

Hi,

I am trying both suggestions.

I have tried:

<CODE>

DECLARE @myTableTABLE

INSERT @myTABLE

SELECT

productId

FROM

tblProduct

SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*))

FROM @myTable

GO

<\CODE>

..getting an error

“Incorrect syntax near the keyword 'INSERT'”

..struggling here.

Many thanks,

yogiberr

dogeth Mr or Mrs. 500 Points: 576 More actions · Answer 5

How about something like...

select case when A=B then 1 else 0 end

from

(

SELECT

CHECKSUM_AGG(BINARY_CHECKSUM(*)) as A

FROM

Table1

) RC1

,

(

SELECT

CHECKSUM_AGG(BINARY_CHECKSUM(*)) as B

FROM

Table2

) RC2

Cheers,

Kevin

yogiberr Hall of Fame Points: 3679 More actions · Answer 6

Hi Kevin,

ta for the reply.It works nicely if i substitute the tablenames with the names of 2 UDF's that contain the queries from which I want to retrieve the result sets.

<CODE>

select case when A=B then 1 else 0 end

from

(

SELECT

CHECKSUM_AGG(BINARY_CHECKSUM(*)) as A

FROM

udfOne

) RC1

,

(

SELECT

CHECKSUM_AGG(BINARY_CHECKSUM(*)) as B

FROM

udfTwo

) RC2

<\CODE>

Thanks.I tried to implement it to compare the results from 2 SPROCS, but couldn't manage it.

This will do nicely though.

thanks all, for the help.

yogiberr Hall of Fame Points: 3679 More actions · Answer 7

Hi,

I also wanted to use Preeshi's script as a solution, as it offers other functionality based on the arguments that his script is called with.

I contacted regarding the use of resultsets as opposed to tables.Here is the reply.

<<

As an immediate solution, we can copy the results of the procedures into a table (may be a temporary table) and compare.

Since the record set has to be used more than once it is always better to use a temporary table

Insert into #Temp1 exec Proc1

Insert into #Temp2 exec Proc2

sp_Compare2Tables #Temp1, #temp2

Of cource you may get an error saying the tables are not available. In that case, remove that validation part from the coding.

>>

I followed the advice and I do the following:

<CODE>

CREATE TABLE #MyTempTable2(prodId INT PRIMARY KEY)

INSERT INTO #MyTempTable2 exec spProductsIdsGet

CREATE TABLE #MyTempTable1(prodId INT PRIMARY KEY)

INSERT INTO #MyTempTable1 exec spProductsIdsMAXGet

sp_Compare2Tables #MyTempTable2, #MyTempTable, 1

<\CODE>

I get the error:

"Incorrect syntax near '#MyTempTable2"

hmm, It seems that the "1" parameter is causing the problem.

Can someone post a line of code that correctly calls the script (using more than 2 arguments) without error?

I'm hopeful that I could then work out why I am going wrong from there.

Thanks folks,

yogi.