Tsql Update Table with other table

Question

I have two table sql

I want to update table A with data from table

my issu is that

if (element exist update else update)

table a

• clubid | memeber
• 1 |100
• 2 |150

table b

• clubid | memeber

• 1 |200

• 9 |150
• 6 |50

I want table a will update like :

• clubid 1 = > updated
• clubid 9 and Clubid 6 = > will be insert

at the end

table a will be like that

• clubid | memeber
• 1 |200
• 2 |150
• 9 |150
• 6 |50

I can t figure out how to do it can you please help me

Answer 1

you can use Merge.

merge tableA trg
using tableB src on trg.clubid=src.clubid
when matched then
update set trg.member=src.member
when not matched by trg then
insert(clubid,member)
values (src.clubid,src.member);

Answer 2

Two separate statements would do it:

UPDATE TableA SET TableA.Member = TableB.Member 
FROM TableB 
WHERE TableA.ClubID = TableB.ClubID

INSERT INTO TableA
SELECT * FROM TableB WHERE ClubID NOT IN ( SELECT ClubID FROM TableA )