Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How To compare result sets of same PROC in 2 different environments Expand / Collapse
Author
Message
Posted Wednesday, February 26, 2014 5:10 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, October 27, 2014 6:52 AM
Points: 90, Visits: 182
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
Post #1545644
Posted Wednesday, February 26, 2014 8:08 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, November 7, 2014 9:34 AM
Points: 3,989, Visits: 7,171
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"
Post #1545671
Posted Wednesday, February 26, 2014 8:10 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:42 PM
Points: 10,340, Visits: 13,341
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

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
Post #1545672
Posted Thursday, February 27, 2014 6:07 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, October 27, 2014 6:52 AM
Points: 90, Visits: 182
Thanks that helped me a lot.

Regards
DJ
Post #1545816
Posted Thursday, February 27, 2014 6:29 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, November 7, 2014 9:34 AM
Points: 3,989, Visits: 7,171
NO problem, glad it helped!

______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience"
Post #1545837
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse