Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Server 2005 implementation of MySQL REPLACE INTO?

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 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?

like image 960
Michael Stum Avatar asked Aug 01 '08 22:08

Michael Stum


People also ask

Can I use SQL Server instead of MySQL?

Both MySQL and SQL Server are developed as binary collections. But, SQL Server is far more secure than MySQL. MySQL allows developers to access and manipulate files in the database through binaries at run time. Plus, it also allows other processes to access and modify files at run time.

Is SQL 2005 still supported?

After 10 great years, extended support for all versions of SQL Server 2005 is coming to an end on April 12, 2016.

Why use SQL Server instead of MySQL?

SQL Server allows canceling query execution midways. MySQL blocks the database while taking the backup. SQL Server does not block the database during backup process. MySQL is free to use.


4 Answers

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

like image 129
Keith Avatar answered Oct 08 '22 03:10

Keith


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.

like image 32
Karl Seguin Avatar answered Oct 08 '22 03:10

Karl Seguin


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.

like image 38
Dillie-O Avatar answered Oct 08 '22 03:10

Dillie-O


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.

like image 32
Sam Saffron Avatar answered Oct 08 '22 03:10

Sam Saffron