SQL Server 2005 实现 MySQL REPLACE INTO?

2022-01-01 00:00:00 sql-server-2005 mysql sql-server

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总是有点痛苦，尤其是在应用程序中这样做时，因此总是保留 2 个版本的语句.

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 让我烦恼的地方 (在我的博客上咆哮).我希望 MSSQL 支持 upsert.

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

相关文章