SQL Server 2005 implementation of MySQL REPLACE INTO?
Solution 1
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 :-(
Solution 2
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.
Solution 3
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.
Solution 4
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.
Michael Stum
The same thing we do every night, Pinky. Try to take over the world! Full-Stack Developer on Stack Overflow Enterprise, working to make our little corner of the Internet better for all of us.
Updated on July 08, 2022Comments
-
Michael Stum almost 2 years
MySQL has this incredibly useful yet proprietary
REPLACE INTO
SQL Command.Can this easily be emulated in SQL Server 2005?
Starting a new Transaction, doing a
Select()
and then eitherUPDATE
orINSERT
andCOMMIT
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?
-
Michael Stum over 15 yearsGreat, Thanks! Saves the Select and often does not even need a teransaction in situations where i can be sure that between the Update and "my" insert, there is no other insert for that key.
-
Sam Saffron over 15 years@Michael You better have a unique index on this table and handling for duplicate key errors if you are going to use this solution.
-
Evgeniy Berezovsky almost 11 years@Keith Your merge statement doesn't work.
MERGE
does not support theWHERE
clause, you have to rewrite that usingUSING
andON
. Also, unless you addWITH (HOLDLOCK)
, there's a race and concurrentINSERT
s might happen, with one of them failing due to the key clash. -
Triynko over 10 yearsYes, as pointed out here: weblogs.sqlteam.com/dang/archive/2009/01/31/… MERGE is not atomic. It takes out an implicit update lock, but releases it before performing an insert, which causes a race condition that can result in primary key violations. You must use an explicit HOLDLOCK in addition to the implicit UPDLOCK in order for the operation to be atomic. As it stands, it is not atomic, despite appearing to be a single statement.
-
Larry over 10 yearsThe MERGE syntax is wrong and it is fixed in a more recent answer from the same author: stackoverflow.com/a/243670/24472
-
Elan over 9 yearsThe above insert, followed by an update can still fail. With a unique key you can get duplicate key exceptions on the insert. I recommend adding to your update statement: UPDATE table WITH (SERIALIZABLE) SET ...