Copy Data from one DB Table to the same table in another DB 1

[engineer2100]

Hi Guys,
I have to do something like this:

1. I have a Table X in my DB1
2. I also have the same Table X in another Database
DB2
3. X in DB1 is Superset of X in DB2.
4. Now I want to find out the differences between
the Two tables from DB1 and DB2 and either be
able to insert/update/delete from X@DB2 based on
the Data in X@ DB1.
5. I need something that can find these Differences
and also take the approp. action
[Insert/Update/Delete] on the X@DB2.

Hope I have made it as crystal Clear as possible for you experts.

Regds,
Engi

 

[engineer2100]

Further, I do know that this can somehow be done using a Manual Materialized View. But we donot have the luxary of having a DB link between the Database. So I was wondering if there is someother means of achieving the same

I hope the gurus will take my query seriously and help me out of this

Regards & Thanks
Engi

 

[SantaMufasa]

Engi,

You can do what you want in three pretty simple SQL DML statements. The way I have the three statements written, however, does use a simple database link, but not a Materialized View. (There should not be anything preventing your using simple database links so long as you have a working tnsnames.ora file.)

In this example, I have tried to duplicate your scenario as exactly as I can. Here, then is a walk-through of the code:

==========================================
SQL> select * from x@db1 order by id;

        ID NAME
---------- ---------------------------------------
         1 North America
         2 South America
         3 Africa / Mid-East
         4 Asia
         5 Europe
         7 Australia
         8 Pacific Islands

7 rows selected.

SQL> select * from x@db2 order by id;

        ID NAME
---------- ---------------------------------------
         1 North America
         2 South America
         3 Africa / Middle East
         4 Asia
         5 Europe
         6 India

6 rows selected.

In the above two tables, then, here are the actions that we must see if the code works:

* Delete: "India" (because exists in DB2 but not in DB1)
* Update: "Africa / Middle East" becomes "Africa / Mid-East"
* Insert: "Australia" and "Pacific Islands"
============================================
In the following statement we DELETE rows from DB2 if the rows exist in DB2 but not in DB1:

SQL> delete X@db2 where id in
  2          (select id from X@db2
  3           minus
  4           select id from X@db1);

1 row deleted.

SQL> 
SQL> select * from x@db2 order by id;

        ID NAME
---------- ---------------------------------------
         1 North America
         2 South America
         3 Africa / Middle East
         4 Asia
         5 Europe

5 rows selected.

Expected Result: "6 India" is gone
============================================
In the following statement we DELETE rows from DB2 that have changed (We'll re-INSERT the changed rows afterward.)
Here is the order of events in the following code (driven by parentheses):
1) Find all rows that match between the tables.
2) Subtract the matching rows from the set of DB1 rows (which leaves all CHANGED and NEW rows)
3) Harvest just the IDs from the set of CHANGED and NEW rows.
4) Delete from DB2 the rows which IDs match the harvested IDs. (Net effect: Changed rows go away).

SQL> delete X@db2 where id in
  2          (select id from
  3                  (select * from X@db1 minus
  4                          (select * from X@db1
  5                           intersect
  6                           select * from X@db2)
  7                  )
  8          );

1 row deleted.

SQL> select * from x@db2 order by id;

        ID NAME
---------- ---------------------------------------
         1 North America
         2 South America
         4 Asia
         5 Europe

4 rows selected.

Expected Result: "3 Africa / Middle East" is gone.
============================================
Lastly, we add in all NEW rows and re-add all CHANGED rows:

SQL> insert into X@db2
  2          (select * from X@db1
  3           minus
  4           select * from X@db2);

3 rows created.

SQL> select * from x@db2 order by id;

        ID NAME
---------- ---------------------------------------
         1 North America
         2 South America
         3 Africa / Mid-East
         4 Asia
         5 Europe
         7 Australia
         8 Pacific Islands

7 rows selected.

Expected Result: "3 Africa / Middle East" added back in as "3 Africa / Mid-East" and both "7 Australia" and "8 Pacific Islands" are part of the DB2 table.
============================================
Success !!!

Here, then, are the three SQL DML to achieve your results:

delete X@db2 where id in
	(select id from X@db2
	 minus
	 select id from X@db1);
delete X@db2 where id in
	(select id from
		(select * from X@db1 minus
			(select * from X@db1
			 intersect
			 select * from X@db2)
		)
	);
insert into X@db2
	(select * from X@db1
	 minus
	 select * from X@db2);

If you like the results, be sure to issue a COMMIT; to save the results.

Let us know if this is what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA @ 07:14 (06Dec03) GMT, 00:14 (06Dec03) Mountain Time)

 

[engineer2100]

Mufasa, you must accept the fact that "you are simply great". You have relieved of the burden my shoulder. I was really worried when I didnot see any replies to my query. Whether you believe it or not, I was sure you would be the first person to give a reply!!!! and you did.

Thank you once again

Regards,
Engi

 

[engineer2100]

Mufasa, I am stuck again. I have Long Columns in the table. Late realisation though. Can you please help me out with this. Yeah the solution what you gave works, but I have some tables that have Long columns.

Help!!!!

Regards
Engi

 

[chetwest]

I would start out by creating a BD-LINK between the two databases. Then write selects that use MINUS or UNION to determine what is different or the same. You can take it a step further and use a PLSQL routine to loop through the results using a cursor and perform your inserts/updates/deletes. Hope this helps.

Chet West

http://www.oracletricks.com

 

[chetwest]

In my last post..I meant to say DB-LINKS...
About your LONGs. The first question would be: what type of data do they contain? In anycase, it is most likely going to require you to scan every record and compare. Hopefully, there is some sort of key value that you can use to specify which records to compare with. Then you will need to use dbms_sql to fetch the long data. You may want to consider writing it into a BLOB or CLOB variable with DBMS_LOB which then also can be used to perform a compare on the two values.

Chet West

http://www.oracletricks.com