SQL — DELETE INTO another table

Mike Huls

May 20, 2021 — 2 min read

Delete this Sansevieria from one pot INTO the other! (Photo by Cottonbro on Pexels)

Delete records from one table and insert them into another table in ONE statement

“Why would I need a query like that?” A delete into statement is advantageous for two main reasons:

The statement is Atomic; either both occur or nothing occurs, i.e. if either the delete or the insert fail, it rolls back the changes.
It’s cheaper: your database only has to look up the records once. Alternatively, executing a separate insert and delete take two lookups
Bragging rights: impress your boss and coworkers

Convinced? “Show me some code already!”. Okay, but first we’ll have to set up some tables to demonstrate the query on. Lets code:

Setup: create some tables with data

As an example we’ll imagine we have a company with a lot of processes. Each process logs its errors into a table called ErrorMessages in order to keep track. Once an error is solved, expired or otherwise irrelevant anymore we can remove it from the ErrorMessages. We’d like to save the Error information though, so we can later analyse which process fails the most. For this we’ll create the ErrorMessageBackup. Our goal is to delete from ErrorMessages into the ErrorMessageBackup table. Lets create our tables first and insert some data into our ErrorMessages.

Executing these queries we see that the content of the ErrorMessages table looks like this:

Performing our query

This is where the magic starts. We want to take out the 404 error from our database. Let’s check out our query and then get into it a bit more.

We’re targeting the ErrorMessage with Id 1. We save this in the @targetId variable. Then we DELETE from the ErrorMessages table, filtering for this Id. Using the OUTPUT we have access to the columns we’ve deleted. We OUTPUT these columns, along with an extra one, into the ErrorMessageBackup table.

That’s it! An easy, safe query that allows you to perform two queries at once.