SQL Server 2005 implementation of MySQL REPLACE INTO?

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 updateupdate tablename set field1 = 'new value',    field2 = 'different value',    ...where idfield = 7--insert if failedif @@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 targetusing (values ('new value', 'different value'))    as source (field1, field2)    on target.idfield = 7when 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 :-(

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.