3 Replies Latest reply on Oct 23, 2015 4:08 AM by Gaz in Oz

    Tabelleninhalte vergleichen

    818b94bc-a3a7-4148-bf8e-3d54007c10f4

      Hallo!

       

      Ich habe bisher vergeblich die Funktion im SQL Developer gesucht, mit der man 2 Tabelleninhalte vergleichen kann.

      Man kann die Struktur vergleichen, aber nicht die Sachdaten.

       

      Hat jemand eine Idee?

       

      Grüße

      Simon

        • 1. Re: Tabelleninhalte vergleichen
          Gary Graham-Oracle

          You said (something like)...

          So far I have searched in vain for the function in SQL Developer with which to compare the content of 2 tables. One can compare the structure, but not the actual data.  Does somebody have any idea?

          You cannot do this out of the box, no.  However, perhaps this old discussion from another forum will help ... Compare data in two Oracle tables

          • 2. Re: Tabelleninhalte vergleichen
            Marwim

            Do you have access to both tables from one connection (same user, same db, via DB link) or do you need to compare across connections?

            Tha later could be achieved in SQL Developer by Cross Connection Queries compare same table data from diffirent database

             

            Regards

            Marcus

            • 3. Re: Tabelleninhalte vergleichen
              Gaz in Oz

              Hallo Simon,

               

              Here is one possible way::

              WITH t1 (col1, col2, col3, col4, col5) AS
              (SELECT 1, 'A', 'B', 10,   TO_DATE('01-JAN-2015 00:00', 'DD-MON-YYYY HH24:MI') FROM dual UNION ALL
              SELECT 2, 'X', 'Z', 999,  TO_DATE('02-JAN-2015 00:33', 'DD-MON-YYYY HH24:MI') FROM dual UNION ALL
              SELECT 3, 'T', 'O', 11,   TO_DATE('03-JAN-2015 23:59', 'DD-MON-YYYY HH24:MI') FROM dual UNION ALL
              SELECT 4, 'D', 'H', 2,    TO_DATE('04-JAN-2015 11:00', 'DD-MON-YYYY HH24:MI') FROM dual UNION ALL
              SELECT 5, 'A', 'B', 1000, TO_DATE('05-JAN-2015 12:53', 'DD-MON-YYYY HH24:MI') FROM dual UNION ALL
              SELECT 6, 'L', 'M', -1  , TO_DATE('06-JAN-2015 01:23', 'DD-MON-YYYY HH24:MI') FROM dual
              ),
                   t2 (col1, col2, col3, col4, col5) AS
              (SELECT 1, 'A', 'B', 10,   TO_DATE('01-JAN-2015 00:00', 'DD-MON-YYYY HH24:MI') FROM dual UNION ALL
              SELECT 2, 'X', 'Z', 999,  TO_DATE('02-JAN-2015 00:33', 'DD-MON-YYYY HH24:MI') FROM dual UNION ALL
              SELECT 3, 'T', 'O', 11,   TO_DATE('03-JAN-2015 23:59', 'DD-MON-YYYY HH24:MI') FROM dual UNION ALL
              SELECT 4, 'D', 'H', 2,    TO_DATE('04-JAN-2015 11:00', 'DD-MON-YYYY HH24:MI') FROM dual UNION ALL
              SELECT 5, 'A', 'B', 1000, TO_DATE('05-JAN-2015 12:53', 'DD-MON-YYYY HH24:MI') FROM dual UNION ALL
              SELECT 6, 'L', 'M', -2  , TO_DATE('06-JAN-2015 01:23', 'DD-MON-YYYY HH24:MI') FROM dual
              )
              SELECT col1, col2, col3, col4, col5, MAX(source)
              FROM  (SELECT t.*, 'T1' source
                     FROM   t1 t
                     UNION ALL
                     SELECT t.*, 'T2'
                     FROM   t2 t)
              HAVING COUNT(*) = 1
              GROUP BY col1, col2, col3, col4, col5
              ORDER BY col1;
              
                    COL1 C C       COL4 COL5            MA
                       6 L M         -2 06-JAN-15 01:23 T2
                       6 L M         -1 06-JAN-15 01:23 T1
              
              2 rows selected.
              
              

              Dummy tables (WITH clause generated) are almost identical except row 6...


              Cheers,


              Gaz