locked
Comparing Tables RRS feed

  • Question

  • I have to tables that are identical. These tables are being replicated and should contain the same number of records and data etc. From time to time, for whatever reason, these tables get out of sync.

     

    Now, there is a need to physically compare the 2 tables. I have a sql that looks at both tables and compare records. The problem is that the output lists all the records. 

     

    At this point I am only interested in the record(s) that exist in one table and not in the other. Can someone help?

     

    ODBC for RGWP Remote Server

    SELECT "SALESTKT"."TICKET_ID", "SALESTKT"."TICKET_DATE", "SALESTKT"."TICKET_TIME"

    FROM   "SALESTKT" "SALESTKT"

    EXTERNAL JOIN SALESTKT.TICKET_ID={?Advantage ODBC for RGWP: SALESTKT_1.TICKET_ID}

     

     

    ODBC for RGWP Local Server

    SELECT "SALESTKT_1"."TICKET_ID", "SALESTKT_1"."TICKET_DATE", "SALESTKT_1"."TICKET_TIME"

    FROM   "SALESTKT" "SALESTKT_1"

    WHERE  "SALESTKT_1"."TICKET_ID"={?Advantage ODBC for RGWP Remote: SALESTKT.TICKET_ID}

      

     

    Monday, September 17, 2007 12:55 PM

Answers

  • I don't know exactly in which enviornment you are working

    There are 2 option which i am working for VS2005 & Crystal Report

     

    Option 1

    Create dataset object, Get data from database & assign to datasource property of Crystal report document object

     

    Option 2

     

    Create Report thru Stored procedure, at runtime pass values to parameters of stored procedure

     

    In both option you can write query as you want

     

    Let me know if you want something else.

     

    Hope it helps

    Wednesday, September 19, 2007 5:09 AM

All replies

  • I think you are asking about SQL & not about Crystal Report. This forum is only for crystal report related questions.

    May be i wrong but it seems to be.

     

    I am not sure but SQL server don't have external join. They have full join

    SELECT

    Column1, Column2

    FROM Table1 FULL JOIN Table2 ON Table1.Column1 = Table2.Column1

    WHERE Table1.Column1  IS NULL OR Table2.Column2 IS NULL

     

    Above query will give records which are not there in Table1 or Table2

     

    Hope it helps

    Tuesday, September 18, 2007 3:48 AM
  • No, this is related to Crystal Reports and how and where to define my sql select.

     

    The case:

     

    2 identical data base tables (RGWP Remote and RGWP Local), 2 tables both with same name SALESTKT, I have prevously defined ODBC connections for each data base and in the data base expert I have selected both connections. (Note: CR renamed last table selected to SALESTKT1 since it already existed after  I selected the first connection).

     

    Now, where do I put the code that compares the 2 tables for identical records. Do I do this in "add command"?  Do I nedd to add a command for each one? I need to read one table and compare to the other table and print any record that do not exist in the other. Here is the code I have, but I am not sure if it will work:

     

    Select SALESTKT.TICKET_ID,
    SALESTKT.TICKET_DATE,
    SALESTKT.TICKET_TIME
    from SALESTKT
    where not exists
    (select 1 from SALESTKT as SALESTKT1 where
    SALESTKT.TICKET_ID = SALESTKT1.TICKET_ID
    and SALESTKT.TICKET_DATE= SALESTKT1.TICKET_DATE
    and SALESTKT.TICKET_TIME = SALESTKT1.TICKET_TIME)

    Tuesday, September 18, 2007 3:23 PM
  • I don't know exactly in which enviornment you are working

    There are 2 option which i am working for VS2005 & Crystal Report

     

    Option 1

    Create dataset object, Get data from database & assign to datasource property of Crystal report document object

     

    Option 2

     

    Create Report thru Stored procedure, at runtime pass values to parameters of stored procedure

     

    In both option you can write query as you want

     

    Let me know if you want something else.

     

    Hope it helps

    Wednesday, September 19, 2007 5:09 AM