Ask Question
Help Others
Home › Blogs › Lepa's blog

Compare Data from the Same Table in two Different Environments

Mon, 04/14/2008 - 1:25pm — Lepa
Your rating: None Average: 5 (1 vote)

One of the requirements I have lately is to compare table data between two different environments (Development and Test).
The Oracle SQL below compares table1 that has 2 key fields and 3 regular fields.

Note: For the SQL below to work, your password needs to be the same in both environments. If not, then
a connection using the below will not be possible.

FROM table1 tst, table1@dev_database dev

-- Compare data from the same table in two different environments
SELECT   tst.fieldkey1, tst.fieldkey2,
         (CASE tst.field1
             WHEN stg.field1
                THEN 'Match'
             ELSE    'field1 in DEV is '
                  || dev.field1
                  || '. field1 in TEST is '
                  || tst.field1
          END
         ) AS field1,
         (CASE tst.field2
             WHEN dev.field2
                THEN 'Match'
             ELSE    'field2 in DEV is '
                  || dev.field2
                  || '.  field2 in TEST is '
                  || tst.field2
          END
         ) AS field2,
         (CASE tst.field3
             WHEN dev.field3
                THEN 'Match'
             ELSE    'field3 in DEV is '
                  || dev.field3
                  || '.  field3 in TEST is '
                  || tst.field3
          END
         ) AS field3
    FROM table1 tst, table1@dev_database dev
   WHERE tst.fieldkey1 = dev.fieldkey1
     AND tst.fieldkey2 = dev.fieldkey2
     AND (   tst.field1 <> dev.field1
          OR tst.field2 <> dev.field2
          OR tst.field3 <> dev.field3
         )
ORDER BY tst.fieldkey1, tst.fieldkey2;

Bookmark/Search this post with
  • Buzz Up!
  • del.icio.us
  • Digg
  • Facebook
  • Google Buzz
  • Reddit
  • StumbleUpon
  • Technorati
  • Twitter
  • Yahoo
Please try to help out with unanswered topics on the forum. Chances are you have had the same issue/question some time in your IT career!

Comments

Have a question? Please ask it on the forum instead.
Sat, 11/01/2008 - 9:32pm
#1
warhammer's picture
warhammer (not verified)
thanks for the sharing the info

thanks for the sharing the info

Top
  • reply
Sun, 11/02/2008 - 12:12am
#2
HH's picture
HH
User offline. Last seen 1 year 11 weeks ago. Offline
Joined: 07/09/2008
Posts: 17
Re: Compare Data from the Same Table ...

Lepa,
Thank for sharing.

I don't think that the password needs to be the same. The db link user needs to have select access to the relevant table.

Also, I have successfully used minus operator.

SELECT 'ENV1', col1, col2, col3
FROM table
MINUS
SELECT 'ENV1', col1, col2, col3
FROM table@dblink
UNION ALL
SELECT 'ENV2', col1, col2, col3
FROM table@dblink
MINUS
SELECT 'ENV2', col1, col2, col3
FROM table
ORDER BY 2,3,1

If there are multiple tables to compare, this is easier to build. Also, rows in one table and not the other get picked up which will not show up in the sql you are using.

Top
  • reply
Wed, 04/15/2009 - 2:27am
#3
sipha.bidli's picture
sipha.bidli (not verified)
Re: Compare Data from the Same Table in two Different ...

hi guys i am tryin to compare data from two identical tables E6900 and M9000 and this my DBlink ops$mis_exec.mis_sub_prof@prod
so my query is as follows i am not sure whether i am on the right track

select * from ops$mis_exec.mis_sub_prof@prod minus select * from ops$mis_exec.mis_sub_prof;

Top
  • reply

Post new comment

The content of this field is kept private and will not be shown publicly.
CAPTCHA
The question below is to prevent automated spam submissions.
Image CAPTCHA
Enter the characters shown in the image.