Move records to a history table using Output clause

Delete a record from the source table and move the contents of the deleted row(s) to a history table in a single statement.

The following illustrates how to move a record to a history table using the DELETE statement with the OUTPUT clause.

-- delete a row
DELETE from a
       -- output clause to insert the deleted row to the history table
       output deleted.lRowID, deleted.MyField1, deleted.MyField2, deleted.sLastUser, deleted.dtLastMod, deleted.sCreatedBy, deleted.dtCreateDate, deleted.uuid
       into Test1_hist (lRowID, MyField1, MyField2, sLastUser, dtLastMod, sCreatedBy, dtCreateDate, uuid)
from Test1 a
where a.lRowID = 1002 -- select a specific record to delete