value - SQL Server 2005 implementation of MySQL REPLACE INTO?




sql update replace (3)

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?


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.


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.







sql-server-2005