Merging tables for new information 1

[Kelin]

I know 9i has the MERGE statement but was looking for a way to do this with 8i. To take a source table and compare to a destination table, if the data is different or new to update the destination table.

 

[did02]

You can use a SQL or PL/SQL statement.

If your tables are not too big (SQL):

insert into destination_table
( select * from source_table
where source_table.pk_fields in
(select pk_fields from source_table
minus
select pk_fields from destination_table));

update destination_table
set destination_table.field1=source_table.field1,...
where destination_table.pk_fields in
(select pk_fields from source_table
intersect
select pk_fields from destination_table)
and destination_table.pk_fields=source_table.pk_fields;

If your tables are big (PL/SQL):

open a cursor on your source_table.
loop
update destination_table
set destination_table.field1=source_table.field1,...
where destination_table.pk_fields =cursor.pk_fields;

if SQL%notfound then
insert into destination_table
destination_table.pk_fields =cursor.pk_fields,
destination_table.field1=source_table.field1,...


I hope this can help you.
Rgds,
Did02

 

[Kelin]

That does help, thank you very much.