Update and insert new rows in a single SQL query

Have you ever wondered whether you can update existing rows and insert (or delete) new ones in a single query? If so, find out why Arthur Fuller believes the point and the mistake behind this question are the same.

In member discussion posts, I've noticed some iteration of the following question crop up several times, "Can I update existing rows and insert (or delete) new ones in a single query?" To me, the point, and the mistake, is the same. I'll explain.

I learned to program using procedural languages, in which any given procedure does exactly one thing; and, if you need to do three things, you write three procedures and invoke them successively.

Almost everyone programs in OOP now, using objects and their methods. SQL programmers cannot yet write stored procedures and functions using these concepts (but you will be able to in Yukon). However, OOP and procedural programming aren't that far apart. In an OOP language, the same principle for procedural languages applies: each method should do exactly one thing; and, if it must perform subtasks, then each of these should be an atomic unit, which does exactly one thing.

Let's revisit the original question. Even if you could, why would you want to write a query that updates existing rows and also inserts new rows? Why not write two queries, the former performing the updates and the latter the inserts? What do you gain by staying up nights figuring out the SQL that can do this? And, more importantly, what do you lose?

The answer is transparency. Any such query, assuming that it can be written, is bound to be almost incomprehensible to the average new hire; whereas a pair of queries that are each devoted to one task is blatantly clear.

This perspective applies to stored procedures, updateable views, and dynamic SQL queries. When I see code that performs several different actions on several different tables, it reminds me of the Saturday Night Live skit about the product that is both a dessert topping and a floor wax. If you really need a stored procedure like that, write three: DessertTopping, FloorWax, and Both—the latter containing calls to the former two. Then, simply update these rows and insert those rows. The overhead in calling a stored procedure is the least of your worries.

I call these procedures Atomic and Molecular. Atomic procedures do exactly one thing, while Molecular procedures don't do any work on their own; instead, they invoke several Atomic procedures in the framework of a transaction, or unit of work.

TechRepublic's free SQL Server newsletter, delivered each Tuesday, contains hands-on tips that will help you become more adept with this powerful relational database management system. Automatically subscribe today!