What's a best-practices way to perform an Insert/Update?

[sherifffruitfly]

Hi all,

Given key cols/data cols, I want to:

IF record with that key exists, UPDATE the record with new data value,
IF record with that key does NOT exist, insert the record.

Oracle has a MERGE command, which may be used for this purpose, but
that's not portable. What's a portable good adonet way to do this?

Thanks,

cdj

 

[Earl]

I use the simple technique of checking for an existing positive primary key
as compared to a negative dataset-generated primary key. Set your
auto-generated dataset primary key columns to -1 seed and -1 increment.

Pseudo-code:

If Pri-Key > 0 then
Update
Else
Insert
End if

 

[Jim Brandley]

That works, but requires two round trips to the DB server. I would use a
stored procedure and do it in one round trip.

 

[Earl]

Nope, just uses the existing data in the dataset -- you either update or
insert (assuming the decision is based upon just one record). However, I've
also done it with a stored procedure transaction, but found that it didn't
fit in as neatly with the whole strongly-typed approach. If you were
updating and inserting a batch, the transaction would be the better way to
go.