INSERT record if doesn't exist in other table 1

[cuetzpalin]

Hi,

I have a table that contains "daily" data and another table containing "historical" data.

I need to insert the "daily" data into the "historical" data but I don't want any duplicates added to the "historical" table if the "daily" record already exists.

Can you please provide the INSERT statement to accomplish this? Thanks!

 

[Turkbear]

Hi,
Try this from Ask Tom (

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5318183934935

How can the Merge functionality be implemented in 8.1.7?



Followup June 1, 2004 - 4pm US/Eastern:

takes two steps

update ( select table_merging_into_columns (eg: existing table),
table_containing_updates_columns
from table_merging_into, table_containing_updates
where ... )
set table_merging_into_columns = table_containing_updates_columns
/

insert into table_merging_into
select *
from table_containing_updates
where (primary_key) NOT IN ( select primary_key from table_merging_into);

To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[carp]

Or

INSERT INTO historical_table
(SELECT * FROM daily_table
 MINUS
 SELECT * FROM historical_table);

Of course, this assumes the two tables have identical structures.

 

[Turkbear]

Hi,
Great idea carp, that should work a treat assuming the structures match - a star for you for coming up with an elegantly simple solution.



To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[carp]

Thanks, Turkbear. If you want a simple solution, ask a simple person. If you want an efficient solution, ask a lazy person. I can fill both squares!