SQL Comparing two tables - Need Advice

[john9]

I need advice on a SQL problem. Here is the background info:

I have two Oracle 9i tables. The column definitions in both tables are almost the same. One table has more defined attributes that the other. Each table are defined in separate schemas. User ID permission levels allows access to both schemas. The primary key definitions are the same in both tables.

I am setting up an Oracle trigger in which when the primary table (let's call it Table_A) encounters new rows to be inserted a trigger condition is invoked that will execute a stored procedure. This stored procedure compares all the rows from the primary table (Table_A) to the alternate table (let's call it Table_B) matching on the primary key fields in both tables.

When the primary key value from Table_A is not found in the alternate table (Table_B) I want to setup code that will insert the contents from Table_A to Table_B.

To illustrate the problem here is some example code for your review -

insert Table_B
(
reservation_pk,
name,
address,
reservation_date
)
select
p.reservation_pk,
p.name,
p.address,
" "

from dbo.Table_A p

where not exist
reservation_pk = p.reservation_pk

Can anyone provide me with a good SQL example?

Thanks

 

[SantaMufasa]

John,

Although I cannot justify why you want blank spaces in your "reservation_date" column, here is the code you need to do what you want:

insert into table_b (reservation_pk,name,address,reservation_date)
select p.reservation_pk, p.name, p.address, '    '
from dbo.Table_A p, Table_b b
where p.reservation_pk = b.reservation_pk(+)
and b.reservation_pk is null
/

Try it and let us know that it worked for you.

Mufasa
(aka Dave of Sandy, Utah, USA @ 01:45 (11Jun04) UTC (aka "GMT" and "Zulu"), 18:45 (10Jun04) Mountain Time)

 

[john9]

Thanks Dave, I will try this.