SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software Kodyaz Development Resources
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.

Software Development



UPSERT or in SQL Server 2005

You may have heard the UPSERT functionality in sql statements. In short an upsert statements UPDATEs records if they are existing in the table or in the selected row set else INSERTs records to the table as new rows within a single sql command.

You may also read or heard about the UPSERT functionality will be available with the new SQL Server edition SQL Server 2005 (aka YUKON)

It is true that this functionality has existed in the beta versions of the SQL Server 2005 with the name "Insert with Merge" and in the syntax of MERGE INTO. The syntax is also has been explained with samples on some books but in the RTM version of the SQL Server 2005 this command is also removed.

So do not bother yourself if you are trying to run the command

MERGE INTO MyTable
     USING MyTempTable
          ON MyTempTable.MatchingField1 = MyTable.MatchingField1
WHEN MATCHED THEN
     UPDATE UpdateField1 = MyTempTable.UpdateField1
WHEN NOT MATCHED THEN
     INSERT VALUES(MyTempTable.MatchingField1, MyTempTable.UpdateField1)

and getting the error message

Incorrect syntax near the keyword 'INTO'.

If you are using the INTERSECT or EXCEPT in such queries shown below,

UPDATE CustomersA FROM (CustomersB INTERSECT CustomersA)

go

INSERT INTO CustomersA FROM (CustomersB EXCEPT CustomersA)

go

You will still fail with the error message

Incorrect syntax near the keyword 'FROM'.

 

Instead you can still run 2 queries to get the same upsert functionality, first run an update command for the rows that exist in both record sets or tables then an insert command for the records that does not exist in the target row set or the table.

UPDATE CustomersA
   SET CustomerName = B.CustomerName
FROM CustomersA A (NoLock)
INNER JOIN CustomersB B (NoLock) ON A.CustomerId = B.CustomerId

And later run the Insert command

INSERT INTO CustomersA (
   CustomerId,
   CustomerName
)
SELECT
   Id,
   Name
FROM CustomersB (NoLock)
WHERE
   Id NOT IN (
      SELECT CustomerId FROM CustomersA (NoLock)
   )
Published Sunday, November 05, 2006 3:03 PM
Filed Under:

Comments

 

eralper said:

Instead of Upsert, and "Insert with Merge" or "Merge Into", Microsoft SQL Server 2008 November CTP has the "MERGE" T-SQL statement which enables Update when matched and Insert when not exists functionality.

You can try the Katmai CTP5 for "Merge"

Eralper
December 5, 2007 1:40 AM
 

eralper said:

Hello,

For sample code of T-SQL Merge statement, you can review the article titled MS SQL 2008 Merge T-SQL Enhancement at http://www.kodyaz.com/articles/SQL2008-merge-command.aspx


August 12, 2008 12:26 AM
Anonymous comments are disabled

About eralper

SQL Server administrator and T-SQL developer including BI components Web programming with HTML, HTML5 and ASP, ASP.NET and .NET Framework developer SAP ABAP and SAPUI5 development, Web Dynpro and Smart Form Siemens HiPath Center CTI development
Copyright © 2004 - 2017 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems