SQL Server 2005 implementation of MySQL REPLACE INTO?


Translate

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

Can this easily be emulated in SQL Server 2005?

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.

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


Alle Antworten
  • Translate

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


  • Translate

    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.


  • Translate

    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.


  • Translate

    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.

    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.