Sql Server 使用技巧 - 插入更新
在对性能苛求的场景，可以选用MERGE语句，实现Upsert功能。以下是MERGE语句的优点：
- 快 数据库引擎需要解析编译每次的执行，如果使用多个执行语句必降低性能。
- 简 MERGE语句比较简单易懂
- 省 在同一个事务中处理不需要额外写事务成功失败的逻辑
- 多 功能更多，除了匹配更新，不匹配插入，还可以实现匹配删除，不匹配更新等操作
CREATE TABLE MyTable
(
id INT PRIMARY KEY ,
name VARCHAR(100) ,
salary MONEY
);
GO
INSERT INTO MyTable
SELECT 1 ,
'Catherine Donnel' ,
200000;
INSERT INTO MyTable
SELECT 2 ,
'Stacey Kost' ,
150000;
INSERT INTO MyTable
SELECT 3 ,
'Jason Leanos' ,
36000;
INSERT INTO MyTable
SELECT 4 ,
'Catherine O''Donnel' ,
20000;
INSERT INTO MyTable
SELECT 5 ,
'Rainbow Dance' ,
20000;
GO
CREATE TABLE MyTable2
(
id INT PRIMARY KEY ,
name VARCHAR(100) ,
salary MONEY
);
GO
INSERT INTO MyTable2
SELECT 1 ,
'Catherine O''Donnel' ,
220000;
INSERT INTO MyTable2
SELECT 2 ,
'Stacey Kostue' ,
230000;
INSERT INTO MyTable2
SELECT 4 ,
'Catherine Bonaparte' ,
20000;
INSERT INTO MyTable2
SELECT 9 ,
'Irina Zolotrova' ,
40000;
INSERT INTO MyTable2
SELECT 5 ,
'Eva Jane' ,
40034;
GO
MERGE INTO MyTable AS Target
USING MyTable2 AS Source
ON Target.id = Source.id
WHEN MATCHED THEN
UPDATE SET Target.name = Source.name ,
Target.salary = Source.salary
WHEN NOT MATCHED THEN
INSERT ( id, name, salary )
VALUES ( Source.id ,
Source.name ,
Source.salary
);