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, code samples and tools and downloads for ASP.Net, SQL Server, R Script, Windows, Windows Phone, AWS, SAP HANA 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

     USING MyTempTable
          ON MyTempTable.MatchingField1 = MyTable.MatchingField1
     UPDATE UpdateField1 = MyTempTable.UpdateField1
     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)


INSERT INTO CustomersA FROM (CustomersB EXCEPT CustomersA)


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 (
FROM CustomersB (NoLock)
   Id NOT IN (
      SELECT CustomerId FROM CustomersA (NoLock)
Published Sunday, November 05, 2006 3:03 PM
Filed Under:



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"

December 5, 2007 1:40 AM

eralper said:


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 - 2020 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems