Merrill Aldrich : Handy Trick: Move Rows in One Statement

Moving data around has never been so easy.

The fact that we can take output from DELETE and feed it to INSERT actually models what we are trying to do perfectly. And, we get some advantages:

1. This is now a single, atomic statement on its own.
2. The logic about which rows to move is specified only once, which is neater.
3. The logic about which rows to move is only processed one time by the SQL Server engine.

Merrill Aldrich : Handy Trick: Move Rows in One Statement