关于sqlserver 的merge 操作
简单记录一下,sqlserver中的merge相当于 mysql 中的 Insert into on duplicated
code:
create table mergetest(
id uniqueidentifier primary key,
name varchar(50)
)
--insert into mergetest(id,name) values(newid(),'kevin');
select * from mergetest;
merge into mergetest as T
using (
values
('5E8BF9D8-8479-40BE-BF28-85F3C0336B68','newKevin'),
(newid(),'Lanister')) AS sourceMergeTest(id,name)
ON T.id= sourceMergeTest.id
WHEN MATCHED THEN
UPDATE
set name=sourceMergeTest.name
WHEN NOT MATCHED THEN
INSERT
VALUES(id,name);
因为merge的用法一般情况是连接两张表,所以 如果我们 要做类似于 mysql中的 insert on duplicate的 操作的话 需要将插入的值 模拟城一个新的表,上面的代码就是values 后面的操作 然后 AS 然后 ON
–记录于 16/6/29