Update esle insert ?insert else update ?

[donny750]

hello,

In some ETL, i've the option :
Update else insert.
Insert else update.
I've some difficult to understand the differences between this 2 options.
In fact, with the first or the second option,my date are update and insert.

Somebody can explain me ?
Have exemples ?

Thanks

 

[AlexCuse]

What is your database? In SQL server there is a technique called upsert that does what you speak of. It works like this:

If exists (select * from myTableDest where ID = @param)

update a
set a.ColumnA = b.something
, a.Column1 = b.somethingelse
from myTableDest a
inner join myTableSource b
on a.ID = b.ID
where a.ID = @param

else

insert into myTableDest
select @param, ColumnA, Column1
from myTableSource

Where @param is the unique identifier of the record to be inserted/updated. I am not sure of how to do this with anything but SQL server though.

Hope this helps,

ALex

Ignorance of certain subjects is a great part of wisdom

 

[donny750]

my database is Oracle (one of the best database in the world)

 

[AlexCuse]

Donny -

The best database in the world is one that is properly designed. No database software (even if it is the VERY best in the world) will make up for poor design.

I can't imagine that Oracle does not have the capability to do an 'upsert', but I suggest you try the Oracle forum. Here is the forum for the most recent version:

forum1177

Good luck,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[johnherman]

Agree, check out UPSERT statement. The difference between Insert else Update
and
Update else Insert
depends on which is more likely. Better performance is achieved if the first option occurs more frequently than the second.

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[donny750]

ok
in fact,
If i do more insert than update, i use insert else update and if i do more update than insert i use update else insert;
and it can improve consequently the load performances
it's that ?

 

[jimirvine]

In Oracle, the conditional update or insert structure is the Merge Statement

 

[johnherman]

Yes, that will help performance. I stand corrected - the Oracle command is MERGE. Upsert is a conceptual term.

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[jimirvine]

John, I wasn't meaning to correct you, I realised that you meant UPSERT as a concept. I just noticed that the Oracle structure hadn't actually been named at that point so I thought I'd stick my big nose in