> Hello Group,
>
> I am looking for the best way to insert a record into a table (if it
> doesn't exist) or update the record in the table (if it does exist)
> using a stored procedure.
>
> I have thought about a few ways of achieving this
>
> Method 1:
>
> delete from tableA where ....
> insert into tableA ...
>
> This is not going to work because there are foreign key constraints
> that will cascade the delete and this is not desirable
>
> Method 2:
>
> begin
> insert into tableA ....
> when -803 do
> begin
> -- primary key violation
> update tableA set .....
> end
> end
>
> This looks like it should work OK
>
> Method 3:
>
> update tableA set ....
> if (ROW_COUNT = 0) then
> begin
> insert into tableA .....
> end
>
> This looks like it should work OK as well in FB1.5.
>
> There will be a pretty good mix between records that need to be
> inserted and records that need to be updated, not quite 50-50, but
> probably 60-40 (update-insert).
>
> Which method would perform best under this sort of scenario and why?
>
> Adam

method 3 is best IMO because it's quiet - you could also test to see if the
PK exists first instead of updating. A PK select is as fast.
Alan
Alan