create table [maomao365.com]
(keyId int identity,
info varchar(80)
)
go
insert into [maomao365.com]
(info)values('sqlblog'),
('sqlserver'),('maomao365.com')
---merge into实现如果存在，则更新
---如果不存在，则删除
MERGE INTO [maomao365.com] a
USING (SELECT 2 AS keyId_B, 'other' AS info_B ) b 
ON ( a.keyId = b.keyId_B)
WHEN MATCHED THEN
UPDATE SET a.info= b.info_B --更新
 
WHEN NOT MATCHED THEN
INSERT (info) VALUES(b.info_B); ---插入
 
go
select * from [maomao365.com]
go
MERGE INTO [maomao365.com] a
USING (SELECT 20 AS keyId_B, 'new Info' AS info_B ) b 
ON ( a.keyId = b.keyId_B)
WHEN MATCHED THEN
UPDATE SET a.info= b.info_B --更新
 
WHEN NOT MATCHED THEN
INSERT (info) VALUES(b.info_B); ---插入
go
 
select * from [maomao365.com]
go
truncate table [maomao365.com]
drop table [maomao365.com]