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.
![Blink](images/emotions/blink.gif)
Thanks folks.
yogiberr
January 20, 2004 at 6:43 am
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]
January 20, 2004 at 7:00 am
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.
January 20, 2004 at 7:09 am
Hi Frank,
yes,
I got hold of a script at:
http://qa.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.
January 20, 2004 at 8:29 am
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
January 20, 2004 at 4:29 pm
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
January 21, 2004 at 6:21 am
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.
January 22, 2004 at 5:51 am
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.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply