SQL, copy data from one table to another

Feb 5, 2023

Learn how to copy data from one SQL table to another with INSERT INTO and SELECT, including how to skip the primary key column to avoid duplication errors.

~~~

One of those maintenance tasks: copying data from one table to another.

You can copy all elements from a table into another like this:

INSERT INTO some_table 
SELECT * FROM other_table

Of course you can just select some if you want:

INSERT INTO some_table 
SELECT * FROM other_table WHERE list=94

If the table you’re copying to has existing data, you might have primary key duplication issues.

To leave the primary key column empty to make the table auto-fill it with its auto increment, I selected all columns except the primary key:

INSERT INTO some_table (`age`, `name`, `email`)
SELECT `age`, `name`, `email` FROM other_table

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

Tagged: Database · All topics

~~~