Insert or update multiples rows

Question

I have two tables where TableA has latest data and TableB has some old data. I want to update TableB's data if it matches id with TableA and if doesn't match insert new row in TableB.

I got a solution from stackOverflow

begin tran
if exists (select * from t with (updlock,serializable) where pk = @id)
   begin
   update t set hitCount = hitCount+1
   where pk = @id
end
else
begin
   insert t (pk, hitCount)
   values (@id, 1)
end
commit tran

But it seems I need to pass @id each time, may be I am not getting it in the correct way. I have hundreds of row to update/insert from tableA.

As per the question guide, please do not post images of code, data, error messages, etc. - copy or type the text into the question. Please reserve the use of images for diagrams or demonstrating rendering bugs, things that are impossible to describe accurately via text. — Dale K, Nov 23 '22 at 01:13
You can use merge to do it all in one go - but thats complex. Or you can do 2 statements, and update with join where you join the 2 tables on id plus an insert where you insert with `NOT EXISTS` to check whether the row exists or not. — Dale K, Nov 23 '22 at 01:18
@DaleK Please [don't use `MERGE`](https://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/). It's bugged out. — J.D., Nov 23 '22 at 01:36
[Please do not upload images of code/data/errors.](//meta.stackoverflow.com/q/285551) — Ken White, Nov 23 '22 at 01:51
@DaleK It doesn't change that it's a non-trivial risk in production code. Highly do not recommend, out of all poor choices one can make in SQL Server. It offers no tangible benefits compared to writing individual DML statements as well. Aaron's pretty knowledgeable too. — J.D., Nov 23 '22 at 02:22
@J.D. I don't disagree with most of what you say, there are times when it offers good benefits. — Dale K, Nov 23 '22 at 02:40

score 1 · Accepted Answer · answered Nov 23 '22 at 02:10

Think relationally.

SQL Server always operates sets. A single row is just a set of 1 row.

Here is a simple example of two step update - insert operations

create table #tableA(id int, [year] int, updated_value int)

insert #tableA(id,[year],updated_value)
values
(1,1990,85),
(2,1991,70),
(3,1992,80)

create table #tableB(id int, [year] int, score int)

insert #tableB(id,[year],score)
values
(1,1990,50),
(4,1995,20)

update #tableA set
updated_value=b.score
from #tableA a
inner join #tableB b on a.id=b.id --inner is important

insert #tableA(id,[year],updated_value)
select b.id,b.[year],b.score
from #tableB b
left join #tableA a on a.id=b.id --left is important
where a.id is null -- and this line too

select * from #tableA

If you wish you can combine update and insert in a single merge operation.

merge #tableA as tgt
using #tableB as src
    on src.id=tgt.id
when matched then
    update set updated_value=src.score
when not matched then
    insert(id,[year],updated_value)
    values(id,[year],score)
; -- semicoloumn is required

select * from #tableA

Please [don't use `MERGE`](https://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/), it's bugged out. — J.D., Nov 23 '22 at 02:30

Insert or update multiples rows

1 Answers1