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


How To compare result sets of same PROC in 2 different environments


How To compare result sets of same PROC in 2 different environments

Author
Message
dhananjay.nagarkar
dhananjay.nagarkar
Old Hand
Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)

Group: General Forum Members
Points: 342 Visits: 185
Hi team,
I have a Stored Proc in DEV which yield me 37712 records and the same PROC in production yields me 38000 records, i wanted to know the easiest way using TSQL how to get the records from the PRODUCTION that are not in DEVELOPEMENT for the same PROC.

Thanks
DJ
MyDoggieJessie
MyDoggieJessie
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12802 Visits: 7444
Create a temp table and insert the results from proc1 into it, do the same for proc2

select from temp table 1 use EXCEPT on the select from temp table 2

In it's most basic form (and no points for cute code, but it should get you going int he right direction):

/* Setting up only so we have data for our procedures as a test ONLY */
create table temp1 (col1 int, col2 varchar(1))
create table temp2 (col1 int, col2 varchar(1))

insert into temp1
select 1,'A' union all
select 2,'B' union all
select 3,'C' union all
select 4,'D'

insert into temp2
select 1,'A' union all
select 2,'B'
go

create proc dbo.getresults1 as
select * from temp1
GO

create proc dbo.getresults2 as
select * from temp2
GO

/* ENd of set up */

/* Here is what you would do */
create table #t1 (col1 int, col2 varchar(1))
create table #t2 (col1 int, col2 varchar(1))

insert into #t1
exec dbo.getresults1

insert into #t2
exec dbo.getresults2

So a simple select from both temp tables show:
Temp table 1
col1 col2
1 A
2 B
3 C
4 D

And for the other:
Temp table 2
col1 col2
1 A
2 B

Now you can run your EXCEPT

select * from #t1
except
select * from #t2



Which will output the missing rows:
col1 col2
3 C
4 D

Again, there are MANY WAYS to do you, and this is not pretty or anything, but should get you going in the proper direction

______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience" ;-)
Jack Corbett
  Jack Corbett
SSC-Forever
SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)

Group: General Forum Members
Points: 46599 Visits: 14925
Since you said in T-SQL I won't bother trying a PoSH solution. The way I'd typically do something like that is to use a linked server from DEV to Prod (or vice-versa), dump the results of the 2 procs into temp tables, and do an EXCEPT. Something like this:


INSERT INTO #prod
EXEC linkedservername.databasename.schemaname.procname;

INSERT INTO #dev
EXEC schemaname.procname;

/* this returns all the rows in prod not in dev
SELECT *
FROM #prod
EXCEPT
SELECT *
FROM #dev AS d;



You could also reverse the order of the queries in the EXCEPT to find out if there are rows in Dev not in Prod.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
dhananjay.nagarkar
dhananjay.nagarkar
Old Hand
Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)

Group: General Forum Members
Points: 342 Visits: 185
Thanks that helped me a lot.

Regards
DJ
MyDoggieJessie
MyDoggieJessie
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12802 Visits: 7444
NO problem, glad it helped!

______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience" ;-)
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