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
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?
This is something that frustrates me concerning MSSQL (rant on my blog). I desire MSSQL sustained
@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
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 : - (
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.