SQL Question Insert where not exists

From: Paul Routledge <paul_at_earthbound.u-net.com>
Date: 1996/10/23
Message-ID: <01bbc125$a7590380$778577c2_at_earthbound.u-net.com>#1/1

[Quoted] I have a query about an insert statement. I already have a number of optional statements I can use to acheive the required insert but which one gives best performance and is there a more efficient option.

[Quoted] Basically I want to insert into tableA with a select from tableB and tableC where such a row does not already exist in tableA.

tableA

	key1
	key2
	values...
tabelB
	key1
	values...
tableC
	key2
	values...

possible Insert statement:

---

insert into tableA

        (key1, key2)
select distinct

	tableB.key1,
	tableC.key2
from 	tableB,
	tableC
where not exists
	(select	1
	from	tableA
	where	tableA.key1 = tableB.key1
	and	tableA.key2 = tableC.key2)


--------------------------

or alternatively:

---

insert into tableA

	(key1, key2)
select	tableB.key1,
	tableC.key2
from 	tableB,
	tableC
minus
select	key1,
	key2
from	tableA


--------------------------

The second option is using a full table scan in the select on tableA. However it requires no distinct clause on the first select but because I'm using a minus Oracle still performs a sort on the data.

[Quoted] Does anyone know of an efficient INSERT WHERE NOT EXISTS?

---

Paul Routledge Received on Wed Oct 23 1996 - 00:00:00 CEST