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

  • Article

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.