SQLSERVER如何执行“如果数据表中无数据则插入，有数据则更新”？

时间：2014-04-09 23:57:47 阅读：1363 评论：0 收藏：0 [点我收藏+]

2008之后用Merge语句,
之前就要自己写if条件了.

Merge:

http://technet.microsoft.com/zh-cn/library/cc879317(v=sql.105).aspx

若要提高 MERGE 语句的性能，我们建议您遵循以下索引准则：

对源表的联接列创建唯一的涵盖索引。
对目标表的联接列创建唯一的聚集索引。

SQL Server 索引设计指南：http://msdn.microsoft.com/zh-cn/library/jj835095.aspx

MERGE dbo.FactBuyingHabits AS Target
USING (SELECT CustomerID, ProductID, PurchaseDate FROM dbo.Purchases) AS Source
ON (Target.ProductID = Source.ProductID AND Target.CustomerID = Source.CustomerID)
WHEN MATCHED THEN
    UPDATE SET Target.LastPurchaseDate = Source.PurchaseDate
WHEN NOT MATCHED BY TARGET THEN
    INSERT (CustomerID, ProductID, LastPurchaseDate)
    VALUES (Source.CustomerID, Source.ProductID, Source.PurchaseDate)
OUTPUT $action, Inserted.*, Deleted.*;

======================================================

create table t_test(id int primary key,value varchar(50))

insert into T_Test
values(111,‘aaa‘)
go

declare @id int
declare @value varchar(50)

set @id = 111
set @value = ‘111‘

if exists(select 1 from t_test where id=@id)
update T_Test
set value = @value
where ID = @id
else
insert into T_Test
select @id,@value

--插入重复数据后,发现数据被更新了,没有报错
select *
from t_test