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!