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

  • 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

  • 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

  • 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.

  • Thanks that helped me a lot.

    Regards

    DJ

  • 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