SQL Server 2005实现的MySQL REPLACE INTO吗? [英] SQL Server 2005 implementation of MySQL REPLACE INTO?

问题描述

MySQL具有非常有用但专有的REPLACE INTO SQL命令.

MySQL has this incredibly useful yet proprietary REPLACE INTO SQL Command.

这可以在SQL Server 2005中轻松模拟吗?

Can this easily be emulated in SQL Server 2005?

开始新事务，先执行Select()然后执行UPDATE或INSERT和COMMIT总是很麻烦，尤其是在应用程序中执行此操作时，因此始终保留两个版本声明.

Starting a new Transaction, doing a Select() and then either UPDATE or INSERT and COMMIT is always a little bit of a pain, especially when doing it in the application and therefore always keeping 2 versions of the statement.

我想知道是否有一种简单且通用的方法将这种功能实现到SQL Server 2005中?

I wonder if there is an easy and universal way to implement such a function into SQL Server 2005?

推荐答案

这让我很讨厌MSSQL(

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

@ Dillie-O的代码在较旧的SQL版本(+1票)中是个好方法，但基本上还是两个IO操作(exists然后是update或insert)

@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', ... )

如果是更新，则将其减少为一个IO操作，如果是插入，则将其减少为两个.

This reduces it to one IO operations if it's an update, or two if an insert.

MS Sql2008从SQL:2003标准引入了merge:

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, ... )

现在实际上只是一个IO操作，但是代码很糟糕:-(

Now it's really just one IO operation, but awful code :-(

这篇关于SQL Server 2005实现的MySQL REPLACE INTO吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！