SQL Server 2005 实现 MySQL REPLACE INTO？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/234/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server 2005 implementation of MySQL REPLACE INTO?
提问by Michael Stum
MySQL has this incredibly useful yet proprietary REPLACE INTOSQL Command.
MySQL 有这个非常有用但专有的REPLACE INTOSQL 命令。
Can this easily be emulated in SQL Server 2005?
这可以在 SQL Server 2005 中轻松模拟吗？
Starting a new Transaction, doing a Select()and then either UPDATEor INSERTand COMMITis always a little bit of a pain, especially when doing it in the application and therefore always keeping 2 versions of the statement.
开始一个新的事务，在做了Select()，然后要么UPDATE或INSERT和COMMIT始终是一个痛苦的一点点，在应用程序中做特别是当，因此能始终保持2个版本的声明。
I wonder if there is an easy and universalway to implement such a function into SQL Server 2005?
我想知道是否有一种简单而通用的方法可以在 SQL Server 2005 中实现这样的功能？
采纳答案by Keith
This is something that annoys me about MSSQL (rant on my blog). I wish MSSQL supported upsert.
这让我对 MSSQL 感到恼火（在我的博客上咆哮）。我希望支持 MSSQL upsert。
@Dillie-O's code is a good way in older SQL versions (+1 vote), but it still is basically two IO operations (the existsand then the updateor insert)
@Dillie-O 的代码在旧 SQL 版本中是一个好方法（+1 票），但它仍然基本上是两个 IO 操作（exists然后是updateor insert）
There's a slightly better way on this post, basically:
这篇文章有一个稍微好一点的方法，基本上：
--try an update
update tablename
set field1 = 'new value',
field2 = 'different value',
...
where idfield = 7
--insert if failed
if @@rowcount = 0 and @@error = 0
insert into tablename
( idfield, field1, field2, ... )
values ( 7, 'value one', 'another value', ... )
This reduces it to one IO operations if it's an update, or two if an insert.
如果是更新，则将其减少为一次 IO 操作，如果是插入，则将其减少为两次。
MS Sql2008 introduces mergefrom the SQL:2003 standard:
MS Sql2008merge从 SQL:2003 标准引入：
merge tablename as target
using (values ('new value', 'different value'))
as source (field1, field2)
on target.idfield = 7
when matched then
update
set field1 = source.field1,
field2 = source.field2,
...
when not matched then
insert ( idfield, field1, field2, ... )
values ( 7, source.field1, source.field2, ... )
Now it's really just one IO operation, but awful code :-(
现在它真的只是一个 IO 操作，但是代码很糟糕:-(
回答by Karl Seguin
The functionality you're looking for is traditionally called an UPSERT. Atleast knowing what it's called might help you find what you're looking for.
您正在寻找的功能传统上称为 UPSERT。至少知道它叫什么可能会帮助你找到你要找的东西。
I don't think SQL Server 2005 has any great ways of doing this. 2008 introduces the MERGE statement that can be used to accomplish this as shown in: http://www.databasejournal.com/features/mssql/article.php/3739131or http://blogs.conchango.com/davidportas/archive/2007/11/14/SQL-Server-2008-MERGE.aspx
我不认为 SQL Server 2005 有任何很好的方法来做到这一点。2008 年引入了可用于完成此操作的 MERGE 语句，如下所示：http: //www.databasejournal.com/features/mssql/article.php/3739131或http://blogs.conchango.com/davidportas/archive/ 2007/11/14/SQL-Server-2008-MERGE.aspx
Merge was available in the beta of 2005, but they removed it out in the final release.
Merge 在 2005 年的测试版中可用，但他们在最终版本中将其删除。
回答by Dillie-O
What the upsert/merge is doing is something to the effect of...
upsert/merge 正在做的事情是...
IF EXISTS (SELECT * FROM [Table] WHERE Id = X)
UPDATE [Table] SET...
ELSE
INSERT INTO [Table]
So hopefully the combination of those articles and this pseudo code can get things moving.
所以希望这些文章和这个伪代码的结合可以让事情有所进展。
回答by Sam Saffron
I wrote a blog post about this issue.
The bottom line is that if you want cheap updates ... and you want to be safe for concurrent usage. try:
最重要的是，如果您想要廉价的更新......并且您希望并发使用是安全的。尝试：
update t
set hitCount = hitCount + 1
where pk = @id
if @@rowcount < 1
begin
begin tran
update t with (serializable)
set hitCount = hitCount + 1
where pk = @id
if @@rowcount = 0
begin
insert t (pk, hitCount)
values (@id,1)
end
commit tran
end
This way you have 1 operation for updates and a max of 3 operations for inserts. so, if you are generally updating this is a safe cheap option.
这样你就有 1 个更新操作和最多 3 个插入操作。因此，如果您通常要更新，这是一个安全且便宜的选择。
I would also be very careful not to use anything that is unsafe for concurrent usage. Its really easy to get primary key violations or duplicate rows in production.
我也会非常小心，不要使用任何对并发使用不安全的东西。在生产中很容易出现主键违规或重复行。
