Move SQL data from one table to another

When INSERT followed by DELETE isn't your cup of tea

Sometimes, you may not be able to DELETE rows due to various reasons like maintaining historical data. In these scenarios, mark the transferred rows instead of deleting them:

-- Encountered a wild row yet to be transferred?   
UPDATE old_table
SET is_transferred = 1
WHERE condition
  AND NOT EXISTS (SELECT 1 FROM new_table WHERE old_table.id = new_table.id);

-- Catch 'em all!
INSERT INTO new_table (column1, column2)
SELECT column1, column2
FROM old_table
WHERE condition
  AND is_transferred = 0;


Here is_transferred works as a flag which indicates that a row has been transferred.

One-stone-two-birds approach in SQL Server

If you're using SQL Server, you can move rows in merely one statement with the OUTPUT clause, saving you an extra line and disk I/O:

DELETE FROM old_table
OUTPUT DELETED.column1, DELETED.column2 INTO new_table
WHERE condition;


This command magically deletes from old_table and "abracadabra" they appear in new_table.

Keeping primary keys intact

Conducting IDENTITY_INSERT prior to transferring data can preserve the primary key:

-- "Let the magic happen. No, really, it's just SQL."
SET IDENTITY_INSERT new_table ON;

INSERT INTO new_table (id, column1, column2)
SELECT id, column1, column2
FROM old_table
WHERE condition;

-- Switch off the magic
SET IDENTITY_INSERT new_table OFF;


Just remember to turn IDENTITY_INSERT ON before, and OFF after the operation.

Adapting to different SQL dialects

Different SQL engines (like MySQL, SQL Server, PostgreSQL) come with their distinct syntax, and what's fun without some variety, right?

SQL Server

-- Goodbye from old_table, and hello in new_table
DELETE FROM old_table
OUTPUT DELETED.* INTO new_table
WHERE condition;


MySQL

-- MySQL is out there to prove, magic stands no chance against good old logic
START TRANSACTION;

INSERT INTO new_table
SELECT * FROM old_table
WHERE condition;

DELETE FROM old_table
WHERE condition;

COMMIT;


PostgreSQL

-- PostgreSQL is a little fancy — likes to use RETURNING clause
WITH moved_rows AS (
    DELETE FROM old_table
    WHERE condition
    RETURNING column1, column2
)
INSERT INTO new_table
SELECT column1, column2
FROM moved_rows;


Be race-condition proof

Race conditions might pop out of nowhere when there are transfers happening concurrently. Use serializable transactions or row locks to ensure data consistency:

-- Begin the race
BEGIN TRAN;

-- Locking the competitors
SELECT *
FROM old_table WITH (UPDLOCK, HOLDLOCK)
WHERE condition;

-- Perform a relocate and an erase
INSERT INTO new_table (column1, column2)
SELECT column1, column2
FROM old_table
WHERE condition;

DELETE FROM old_table
WHERE condition;

-- End the race
COMMIT TRAN;


Using UPDLOCK and HOLDLOCK would prevent other transactions from meddling with the specific data until the present transaction is committed.

Encountering common issues

Some issues you might face are:

• Data conflicts: Having primary keys or unique indexes producing violations when transferring data.
• Extended transactions: Lengthy transactions can hamper performance. Batching operations into digestible amounts might help here.
• Foreign key constraints: If the destination tables have dependencies, it's better to disable constraints before the operation, or make sure the dependent data has been transferred.

Being SQL-smart

Here are some pro tips:

• Always test your queries on a non-production environment before running them on production.
• Perform a backup before running commands that can affect many rows. You wouldn't want an "Oh Snap!" moment, would you?
• Monitor performance implications; moving a lot of data could lock up tables and affect availability, something your boss wouldn't be too excited about.