Hi,

> > > merge into t1 tab2
> > > using t1 tab1
> > > on tab1.name = tab2.name and tab1.name = 'ZZZZZ'
> > > when not matched then
> > > insert (type, name, sysid, flag) values (1, 'ZZZZZ', 1, 0)

> Whoops, I spoke too soon as I had tested it on a table with 1 row first and it appeared to work.
>
> The above statement seems to do a separate insert for each row in table which does not match the test.
>
> So, when I tested it on a table which had 100 rows (non matching the 'ZZZZZ' test), it inserted 100 new rows, each with a 'ZZZZ' name.

Yes, if you want to use this trick, the _source_ table should have 1 row.

So this works:

merge
into emp
using (select 'mango' fruits from rdb$database) src
on emp.fruits = src.fruits
when not matched then insert (fruits) values ('mango')

An SP would probably be nicer though - then you can parameterize the input.

Alternatively, you can create a temporary table (or a permanent source table that you empty afer each use), fill it with the fruits you want to insert, and then do this:

merge
into emp
using temp
on emp.fruits = temp.fruits
when not matched then insert (fruits) values (temp.fruits)


Cheers,
Paul Vinkenoog