Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Update esle insert ?insert else update ?

Status
Not open for further replies.

donny750

Programmer
Jul 13, 2006
145
FR
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
 
What is your database? In SQL server there is a technique called upsert that does what you speak of. It works like this:

Code:
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
 
my database is Oracle (one of the best database in the world)
 
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
 
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
 
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 ?
 
In Oracle, the conditional update or insert structure is the Merge Statement
 
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
 
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 :)
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top