stored procedure to insert or update records
-
Hi Guys,
Has anybody got any examples of a simple stored procedure that will insert records from a table in database A into an equivalent table in database B, where the recored exists an update for that record should be performed, and if the record doen not exist than an insert must be performed.
Many thanks
-
This is your basic import-scenario where you may have a staging table loaded with new data that should be updated if exists, or inserted if new.
I usually like to do this with two statements. First an update followed by an insert.
The update is based on a join between both tables, and the insert is based on a left join against the stage-table on only those rows that not exists in the destination.
update a
set a.col1 = b.col1
......
from destination a
join stage b
on a.pk = b.pk
insert destination (col1, col2....)
select b.col1, b.col2....
from stage b
left join destination a
on a.pk = b.pk
where a.pk is null
(..edit... it helps if I write the left join correctly
)..then you're done
/Kenneth
-
thanks for that.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply