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 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 :-(
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.
I wrote a blog post about this issue.
The bottom line is that if you want cheap updates and 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. It's really easy to get primary key violations or duplicate rows in production.