Mat Stephen's SQL Server WebLog

All things SQL Server. Complied by Matthew Stephen - SQL Server Product Specialist, Microsoft UK

Blogs

SQL 2005 - UPSERT: In nature but not by name; but at last!

  • Comments 1
  • Likes

SQL Server has long been criticised for not having an UPSERT command, well now in SQL 2005 we have some good news, we have two new TSQL set operators that give us the complex insert/update logic that constitute an UPSERT.  These are INTERSECT and EXCEPT

e.g.

-- UPDATE Destination FROM (Source INTERSECT Destination)

-- INSERT INTO Destination FROM (Source EXCEPT Destination)

These statements obviate the need to construct complex correlated sub-queries or WHERE EXISTS logic.

And what's more, these operators deliver high performance by exposing core engine functions.

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment