Eryk,

> Its a bit of a strange feature.

Yes, strage indeed, I actually do not see any valid use for it.

> ... I smell a can of worms here. This sort of thing is better handled in
> application level code that "understands" the logical semantics of the
> data it is dealing with.

Could not agree more. If an INERT fails because the PK already exists, then,
in my app anway, it means I have done something wrong, because an exceptions
should not occur when inserting a record (it's supposed to be new and
therefore non-existing). So, in this case I would not like it if an existing
record (which hasn't anything to do with the inserted record) is overwritten
using an UPDATE statement. In a good design I really cannot think of any
valid use for such an INSERTUPDATE statement.

Fons

David,

> Your points are valid, my only concern with having to deal with the
> exception is the speed c# takes to raise the exception, this means
> adding\updating records which may or may not already be in the
> DB takes ages, since every time an exception is raised it takes time.

See my reply to Eryk. Since I consider such a statement bad design of your
app, I see no valid reason to have one. Your app should know if it is a new
record or that you alter an existing one and therefore you should use the
appropriate statement: INSERT if new record and UPDATE if you want to alter
an existing record. Well, this is how my app works.

> Tim can choose a solution according to whichever design his experience
> tells him is best, my only issue here is the speed it takes to get to the
> update once the insert has failed.

If using the appropriate statement, the raised exception notifies you of
your mistake. When using INSERTUPDATE this extra check is gone.

Note, that if Tim decides to add such a statement, it is fine by me, as long
as it is a new statement or an added parameter to the INSERT statement. That
way, I simply do not use this "feature".

Fons

Fons,

<< Could not agree more. If an INERT fails because the PK already exists,
then, in my app anway, it means I have done something wrong, because an
exceptions should not occur when inserting a record (it's supposed to be new
and  therefore non-existing). So, in this case I would not like it if an
existing record (which hasn't anything to do with the inserted record) is
overwritten using an UPDATE statement. In a good design I really cannot
think of any valid use for such an INSERTUPDATE statement. >>

People use it for import situations a lot where the desired result is the
imported data without having to wipe out the existing data.  Sort of like a
union operation.  However, I agree with you about the developer really
needing to know what they're doing when using this sort of functionality.
The results can be very bad if one is not careful.

--
Tim Young
Elevate Software
www.elevatesoft.com

David,

> Your points are valid, my only concern with having to deal with the exception is the speed c# takes to raise the exception, this means adding\updating records which may or may not already be in the
> DB takes ages, since every time an exception is raised it takes time.

In that case you can start a transaction, locate on the PK to determine
if the record already exists, conditionally update or append as
appropriate then commit the transaction.

Eryk

Fons,

> Could not agree more. If an INERT fails because the PK already exists,
> then, in my app anway, it means I have done something wrong, because an
> exceptions should not occur when inserting a record (it's supposed to be
> new and therefore non-existing). So, in this case I would not like it if

I can see how the scenario could arise in real-world cases if the design
relies on natural primary keys. For example, transaction logs of
passangers from airports and you use the passport number as the PK. The
number might already exist or it might not and you want to overwrite the
old details with the new ones if they already exist. This doesn't
justify proprietary SQL extensions though - it can be coded efficiently
with standard mechanisms.

Eryk

Hi Tim,

> People use it for import situations a lot where the desired result is the
> imported data without having to wipe out the existing data.  Sort of like
> a union operation.  However, I agree with you about the developer really
> needing to know what they're doing when using this sort of functionality.
> The results can be very bad if one is not careful.

Ok, good example, still I won't recommend such an approach. I find it not
safe and you happen to agree.

Best regards,
Fons

Hi Eryk,

> I can see how the scenario could arise in real-world cases if the design
> relies on natural primary keys. For example, transaction logs of
> passangers from airports and you use the passport number as the PK. The
> number might already exist or it might not and you want to overwrite the
> old details with the new ones if they already exist. This doesn't justify
> proprietary SQL extensions though - it can be coded efficiently with
> standard mechanisms.

Again, a good example. But even in this case I still would not recommend
using am INSERTUPDATE statement. If the old log is to be replaced by a new
one, then in code you can for example, include in the log that a prior
version existed and maybe even backup it up first.

But even if one just only need to overwrite any existing record, this
approach is still bad. Because, what if you entered a wrong passport number
(people make mistakes) than it will overwrite it without any warning. When
you use INSERT, an exception is raised, than the person who entered the
number can verify it (the name belonging to this number pops up) and can
cancel his action.

So, just plain overwriting is bad design. The example from Tim works out
better, but if I think hard enough, I will probably find something wrong
with it either. I might not be the best *technical* programmer in the world,
far from it, but my logic hardly ever fails   Well, most of the times
anyway.

Best regards,
Fons

I use it for syncornization. I need to sync 10,000 records, I am not concerned about overwiting data, but i cant run an UPDATE if the record I wish to UPDATE is not there, I could search to see if the
record is present first, and perhaps its as fast as my suggested approach of an insertupdate, if it is as fast to do a search and then choose insert or update depending on whether the record is already
present in the database then all well and good, if it takes longer, than im asking for a very fast way to check to see if the record is there, if yes run the UPDATE if not run the INSERT. I need to do this
around 10,000 times and it should not take longer than a few seconds (yes I am asking for alot!). Those are my requirements, gentleman your suggestions please.

DavidS


100,000 monkeys with typewriters

Roy Lambert

David,

<< I use it for syncornization. I need to sync 10,000 records, I am not
concerned about overwiting data, but i cant run an UPDATE if the record I
wish to UPDATE is not there, I could search to see if the record is present
first, and perhaps its as fast as my suggested approach of an insertupdate,
if it is as fast to do a search and then choose insert or update depending
on whether the record is already present in the database then all well and
good, if it takes longer, than im asking for a very fast way to check to see
if the record is there, if yes run the UPDATE if not run the INSERT. I need
to do this around 10,000 times and it should not take longer than a few
seconds (yes I am asking for alot!). Those are my requirements, gentleman
your suggestions please.  >>

You should definitely try the UPDATE, check rows affected, if zero then
INSERT approach also and compare it to the exception response version.  It
might be faster, although I still think that the exception version won't be
that slow.

--
Tim Young
Elevate Software
www.elevatesoft.com