SQL Server MERGE statement - Move data from one table to another

Question

In the MERGE statement below, NewTable and OldTable have the same 3 columns: EmployeeId and DivisionId are nvarchar(50) columns and Unpublished is a bit column.

I need to move all the records from OldTable that have Unpublished = 1, into the NewTable. Hence the INSERT (to NewTable) and then DELETE (from OldTable) statements. I have to use MERGE (as a part of the migration rules).

The INSERT part works fine, but the DELETE part is giving me an error saying "An action of type 'DELETE' is not allowed in the 'WHEN NOT MATCHED' clause of a MERGE statement."

How should the DELETE clause be written in this case?

MERGE NewTable as target
USING OldTable as source
on target.EmployeeId = source.EmployeeId
WHEN NOT MATCHED AND source.Unpublished = 1
THEN INSERT (EmployeeId,DivisionId,Unpublished)
VALUES (source.EmployeeId, DivisionId, source.Unpublished)
WHEN NOT MATCHED AND source.Unpublished = 1
THEN DELETE;

Answer 1

Let's begin by using some formatting that might convince an existing status quo to change:

The people in the comments are veterans. The MERGE statement is seducing in its syntax, but not implemented well and full of dangers. Avoid it.

Now that we got this out of the way, the next thing is to understand what exactly you want to delete. I noticed this in your question:

and then DELETE (from OldTable) statements

The merge statements can perform all of (insert,update,delete) on a single table, the one aliased after the merge, in your case, NewTable. But, you cannot delete from another table (OldTable) within the same merge. What you can do is use an output clause to keep track of which rows were inserted in NewTable so that you can then run a delete on OldTable.