January 25, 2008 at 5:06 am
Hi,
Sub: How to Comapre the data returned by two Storedprocedures
I have a stored procedure which Connects to returns the data retrieved by a select query.So when I execute that stored procedure the result will get displayed on the management studio in a Table format.
Question:- Now I want to execute this on two different Databases (Two Databases will contain the same tables,Same data ).And I want compare the result returned from those two different Databases.
Is there any good way to achieve this problem.suggest If there are any scripts/tools by which we can compare the result of two stored procedures.
Thank you inadvance.
January 25, 2008 at 5:28 am
Create #temp(id int,colums...)
insert into #temp(1 as id,....)
exec server1.dbname.db.proc
insert into #temp(2 as id.....)
exec server2.dbname.db.proc
you will have all the data in a table and you can then compare
January 25, 2008 at 8:07 am
I have Tried it.
I have create a #Temp1 Table with the req column set (Same column set returned by the proc)
INSERT #Temp1(col1,col2)
EXEC Proc1
Then I am getting the error as "An INSERT EXEC statement cannot be nested."
Here Prc1 is calling another Proc2 which contains the INSERT EXEC ....Statement.
Note: Here I am not allowed to do any code change in Proc1,Proc2
Thank You In Advance.
January 25, 2008 at 8:37 am
Change your procs to insert the result data into a temporary table and then compare those two tables.
January 25, 2008 at 8:39 am
Do you have to use the stored procedure or can you perform the select query? Are you looking for differences?
You can use except to get the differences between the two tables. (this query will return everything in the first select that is not existant in the second select.)
select *
from db1.dbo.table
except
select *
from db2.dbo.table
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply