SQLSERVER如何执行“如果数据表中无数据则插入,有数据则更新”?
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
---------------------
MERGE问题举例:
SQL Server使用Merge语句当源表数据集为空时,无法进行查询的问题:http://www.jb51.net/article/19281.htm
原因就是using (select UserName,[Level] from SourceTable where UserName=@UserName) as sr(UserName,[Level]) on tg.UserName=sr.UserName这个语句中,sr结果集为空,所以merge语句就不向后执行了。
--------------------
附加:MYSQL一句sql解决:http://50vip.com/blog.php?i=347