February 26, 2014 at 5:10 pm
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
February 26, 2014 at 8:08 pm
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
col1col2
1A
2B
3C
4D
And for the other:
Temp table 2
col1col2
1A
2B
Now you can run your EXCEPT
select * from #t1
except
select * from #t2
Which will output the missing rows:
col1col2
3C
4D
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; Theyll drag you down to their level and beat you with experience
February 26, 2014 at 8:10 pm
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
Consultant - Straight Path Solutions
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
February 27, 2014 at 6:07 am
Thanks that helped me a lot.
Regards
DJ
February 27, 2014 at 6:29 am
NO problem, glad it helped!
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply