从另一个表更新并插入到一个表 [英] Update and insert to one table from another

问题描述

我有两张桌子:

table1:(ID、代码、名称)
table2: (ID, Code, Name)具有相同的列

table1: (ID, Code, Name)
table2: (ID, Code, Name) with same columns

我想将数据从 table1 插入到 table2 或更新 table2 中存在的列(table1.ID = table2.ID)

I want to to insert data from table1 to table2 or update columns if that exists in table2 (table1.ID = table2.ID)

这样做的简单方法是什么?

What is the simple way to do this?

没有合并

推荐答案

Merge table2 as target
using table1  as source
on
target.id=source.id
When matched 
Then
update 
set target.id=source.id,
    target.name=source.name
When not matched by Target Then
INSERT (id, name) VALUES (id, name);

Merge 语句存在一些问题，因此应与 注意..

There are some issues with Merge statement,so it should be used with caution..

我进一步建议，使用合并作为两个单独的 DML 语句，如下所示..

Further i recommend ,using merge as two seperate DML statements like below..

insert into table2
select * from table1 t1 where not exists (select 1 from table2 t2 where t2.id=t1.id)

update t2
set 
t2.id=t1.id,
t2.name=t1.name
from 
table1 t1
join
table2 t2
on t1.id=t2.id

保罗怀特在他的详细答案..

这篇关于从另一个表更新并插入到一个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！