SQL Server 2005 execution of MySQL REPLACE INTO?

MySQL has this unbelievably valuable yet properitary REPLACE INTO SQL Command.

Can this conveniently be mimicked in SQL Server 2005?

Beginning a new Transaction, doing a Select() and afterwards either UPDATE or INSERT and also COMMIT is constantly a little of a discomfort, specifically when doing it in the application and also consequently constantly maintaining 2 variations of the declaration.

I ask yourself if there is a very easy and also global means to implement such a function right into SQL Server 2005?

0
2019-05-03 22:58:07
Source Share
Answers: 3

This is something that frustrates me concerning MSSQL (rant on my blog). I desire MSSQL sustained upsert.

@Dillie - O is code is an excellent way in older SQL variations (npls 1 ballot), yet it still is primarily 2 IO procedures (the exists and afterwards the update or insert)

There is a somewhat far better means on this post, primarily :

--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 lowers it to one IO procedures if it is an upgrade, or 2 if an insert.

MS Sql2008 presents merge from the SQL :2003 typical :

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 is actually simply one IO procedure, yet horrible code : - (

0
2019-12-03 04:27:56
Source

The capability you're seeking is commonly called an UPSERT. Atleast recognizing what it's called could aid you locate what you're seeking.

I do not assume SQL Server 2005 has any kind of wonderful means of doing this. 2008 presents the MERGE declaration that can be made use of to complete this as received : 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 readily available in the beta of 2005, yet they eliminated it out in the last release.

0
2019-05-07 17:48:32
Source

What the upsert/merge is doing is something to the result of ...

IF EXISTS (SELECT * FROM [Table] WHERE Id = X)
   UPDATE [Table] SET...
ELSE
   INSERT INTO [Table]

So with any luck the mix of those write-ups and also this pseudo code can get points relocating.

0
2019-05-07 17:45:40
Source