Skip to Main Content
  • Questions

  • In SQL developer compare of two tables

Breadcrumb

XMAS_TREE

The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments. Please have a happy and safe festive season and as always, thanks for being a member of the AskTOM community.

Question and Answer

Chris Saxon

Thanks for the question, Pathi.

Asked: July 22, 2022 - 1:19 pm UTC

Last updated: November 30, 2022 - 5:38 am UTC

Version: 18.2

Viewed 1000+ times

You Asked

Hi,

How to compare the two tables in SQL developer same database which performs the following actions as add, delete, modify

Where I can add delete or modify into source table to target table.

Can I get example with procedure for the about task please help me out.

Thanks


Example table A is having old data and table B is having new data so we have compare to tables if any new data comes have to be updated and have to perform add, delete or modify. For this need a procedure with example
Please
Thanks

and Connor said...

select * from table1 fuller outer join table2
on ( matching_columns)

Nulls on either side represent a difference, eg

SQL> create table t1 as select empno, ename, sal from scott.emp;

Table created.

SQL> create table t2 as select empno, ename, sal from scott.emp;

Table created.

SQL>
SQL> delete from t1 where rownum = 1;

1 row deleted.

SQL> insert into t2 (empno,ename) values ( 1000,'Connor');

1 row created.

SQL>
SQL> select *
  2  from t1 full outer join t2
  3  on ( t1.empno = t2.empno );

     EMPNO ENAME             SAL      EMPNO ENAME             SAL
---------- ---------- ---------- ---------- ---------- ----------
                                       7369 SMITH             800
      7499 ALLEN            1600       7499 ALLEN            1600
      7521 WARD             1250       7521 WARD             1250
      7566 JONES            2975       7566 JONES            2975
      7654 MARTIN           1250       7654 MARTIN           1250
      7698 BLAKE            2850       7698 BLAKE            2850
      7782 CLARK            2450       7782 CLARK            2450
      7788 SCOTT            3000       7788 SCOTT            3000
      7839 KING             5000       7839 KING             5000
      7844 TURNER           1500       7844 TURNER           1500
      7876 ADAMS            1100       7876 ADAMS            1100
      7900 JAMES             950       7900 JAMES             950
      7902 FORD             3000       7902 FORD             3000
      7934 MILLER           1300       7934 MILLER           1300
                                       1000 Connor

15 rows selected.


You can customise this however you like to compare rows. Also checkout the ORA_HASH function for comparing row sets



We're not taking comments currently, so please try again later if you want to add a comment.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.