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
