SQLServer2005实现 MySQLREPLACE INTO? ？

MySQL 拥有这个非常有用但是专有的 REPLACE INTO SQL 命令。

在 SQLServer2005中可以很容易地模拟这种情况吗？

启动一个新的 Transaction，执行一个 Select()，然后执行 UPDATE或 INSERT和 COMMIT总是有点麻烦，尤其是在应用程序中执行时，因此总是保留两个版本的语句。

我想知道是否有一种简单的 通用的方法可以在 SQLServer2005中实现这样的功能？

48958 次浏览
小开

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.

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/3739131 or 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.

小开

What the upsert/merge is doing is something to the effect of...

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.

小开
最佳答案

This is something that annoys me about MSSQL (rant on my blog). I wish MSSQL supported upsert.

@Dillie-O's code is a good way in older SQL versions (+1 vote), but it still is basically two IO operations (the exists and then the update or 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.

MS Sql2008 introduces merge from the SQL:2003 standard:

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 :-(

小开

I wrote a blog post about this issue.

The bottom line is that if you want cheap updates and 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.

I would also be very careful not to use anything that is unsafe for concurrent usage. It's really easy to get primary key violations or duplicate rows in production.


提交答案