2 Replies Latest reply: Jan 29, 2010 12:50 PM by Jeff Stevenson RSS

    Comparing the tables in two instances

    744635
      Hi ,

      can any one please let me know is there any way to compare the tables in two instances (Two data sources in DV and PD instnaces ) ASAP????

      Thanks
      Chandra
        • 1. Re: Comparing the tables in two instances
          745714
          To find the differences use the following command:

          Select Emplid, Firstname, Lastname from EMPLOYMENT1 minus
          Select Emplid, Firstname, Lastname from EMPLOYMENT2;

          Thanks - Charlton

          Edited by: user11138951 on Jan 11, 2010 12:18 PM
          • 2. Re: Comparing the tables in two instances
            Jeff Stevenson
            Here is a method of comparing two tables in disparate data sources, with accommodation for the possibility of the data sources being on separate SQL servers.


            --Begin Code

            SELECT CAST('Row in table 1 not matching table 2' as char(40)) as 'Result', * from
            (SELECT * from OPENDATASOURCE(
            'SQLNCLI',
            'Data Source=TABLE1SERVERNAME;integrated security=sspi'
            ).TABLE1DATABASENAME.TABLE1SCHEMANAME.TABLE1TABLENAME
            except
            select * from OPENDATASOURCE(
            'SQLNCLI',
            'Data Source=TABLE2SERVERNAME;integrated security=sspi'
            ).TABLE2DATABASENAME.TABLE2SCHEMANAME.TABLE2TABLENAME) x
            union all
            SELECT CAST('Row in table 2 not matching table 1' as char(40)) as 'Result', * from
            (SELECT * from OPENDATASOURCE(
            'SQLNCLI',
            'Data Source=TABLE2SERVERNAME;integrated security=sspi'
            ).TABLE2DATABASENAME.TABLE2SCHEMANAME.TABLE2TABLENAME)
            except
            select * from OPENDATASOURCE(
            'SQLNCLI',
            'Data Source=TABLE1SERVERNAME;integrated security=sspi'
            ).TABLE1DATABASENAME.TABLE1SCHEMANAME.TABLE1TABLENAME x

            --End Code



            You will need to change the values for the following:

            TABLE1SERVERNAME
            TABLE1DATABASENAME
            TABLE1SCHEMANAME
            TABLE1TABLENAME

            and

            TABLE2SERVERNAME
            TABLE2DATABASENAME
            TABLE2SCHEMANAME
            TABLE2TABLENAME




            Example:


            --Begin Code

            SELECT CAST('Row in table 1 not matching table 2' as char(40)) as 'Result', * from
            (SELECT * from OPENDATASOURCE(
            'SQLNCLI',
            'Data Source=sqlprod;integrated security=sspi'
            ).jde_production.proddta.f0101
            except
            select * from OPENDATASOURCE(
            'SQLNCLI',
            'Data Source=sqlnonprod;integrated security=sspi'
            ).jde_development.testdta.f0101) x
            union all
            SELECT CAST('Row in table 2 not matching table 1' as char(40)) as 'Result', * from
            (SELECT * from OPENDATASOURCE(
            'SQLNCLI',
            'Data Source=sqlnonprod;integrated security=sspi'
            ).jde_development.testdta.f0101
            except
            select * from OPENDATASOURCE(
            'SQLNCLI',
            'Data Source=sqlprod;integrated security=sspi'
            ).jde_production.proddta.f0101) x

            --End Code