SQL, copy data from one table to another

To copy data from one SQL table to another, combine INSERT INTO with a SELECT. The SELECT reads the rows from the source table, and INSERT INTO writes them into the destination table.

It’s one of those maintenance tasks you run into sooner or later. Archiving old records, seeding a staging table, merging data from an import.

How to copy all rows

Here’s the basic form:

INSERT INTO users_archive
SELECT * FROM users

Every row in users gets copied into users_archive.

Notice that SELECT * matches columns by position, not by name. Both tables need the same columns, in the same order, with compatible types. If someone adds a column to one table later, this query breaks. Or worse, it puts data in the wrong column when the types happen to match.

How to copy only some rows

Of course you can just select some rows if you want. Add a WHERE clause:

INSERT INTO users_archive
SELECT * FROM users WHERE list = 94

Only the rows matching the condition get copied.

What about the primary key?

If the table you’re copying to has existing data, you might have primary key duplication issues. The copied rows bring their own id values, and those might already exist in the destination.

To leave the primary key column empty and let the table auto-fill it with its auto increment, select all columns except the primary key:

INSERT INTO users_archive (`age`, `name`, `email`)
SELECT `age`, `name`, `email` FROM users

In my case id was the primary key column, and I left it out.

Listing the columns explicitly is a good habit anyway. The query keeps working even if the two tables drift apart over time, and it also fixes the positional matching problem we saw above.

Copying into a table that doesn’t exist yet

If the destination table doesn’t exist, you can create it and fill it in one statement:

CREATE TABLE users_archive AS
SELECT * FROM users

This works in MySQL, PostgreSQL and SQLite. SQL Server uses a different syntax, SELECT * INTO users_archive FROM users.

Be careful with this one. It copies the column definitions and the data, but not indexes, primary keys or foreign key constraints. If you need those, create the table properly with CREATE TABLE first, then copy the data with INSERT INTO ... SELECT.