Helen,

Will this SQL statement replicate all records present in table1 but not in
table2 ?
INSERT into table2 SELECT * FROM table1 WHERE NOT EXISTS IN (SELECT * FROM
table2)

Is the following statement the best way to retrieve a value from a table and
make sure that the record selected exists, ie the variable contains a valid
value?
if ( not ( exists(SELECT number FROM table1 where (id=1) INTO :number ) ) )
then exception norecord;

Regards

----- Original Message -----
Date: Sun, 01 Jul 2001 15:34:55 +1000
From: Helen Borrie <helebor@...>
Subject: Re: Not in query

At 08:47 PM 30-06-01 -0800, you wrote:
>Can anyone provide an example of SQL syntax that would compare
>the primary index of one table to another to determine whether or
>not a value in one table exists in the other, and if not then
>insert it?


Not in one statement - it can be done with a simple stored procedure,
though.

create procedure conditional_insert(var1 integer, var2 whatever,...)
as
begin
if (not(exists(select pk_table2 from table2 where pk_table2= :var1)))
then
insert into table2(col1, col2, ...)
values(:var1, :var2,.....);
end

Cheers,
Helen

All for Open and Open for All
InterBase Developer Initiative · http://www.interbase2000.org
_______________________________________________________



_________________________________________________________
Do You Yahoo!?
Get your free @... address at http://mail.yahoo.com