Difference between SELECT INTO and INSERT INTO in MySQL

Last updated on September 11th, 2020 at 11:20 am

What is the difference between using SELECT INTO and INSERT INTO? Both can be used to copy data from one table into another

SELECT INTO Syntax

Copy all columns into new table in same database:

SELECT *
INTO new_table
FROM table_name;

Copy only the columns we want into the new table in same database:

SELECT column_name1,column_name2,..
INTO new_table
FROM table_name;

SELECT INTO Examples
Create a backup copy of Customers:

SELECT *
INTO Users_Backup
FROM Users;

Copy only a few columns into the new table:

SELECT User_Name, User_Contact
INTO Users_Backup
FROM Users;

INSERT INTO Syntax

Insert data from one table into another for all columns,

INSERT INTO table2
SELECT * FROM table1;

Insert data from one table into another for specific columns,

INSERT INTO table2
column1, column2..
SELECT column1, column2, ..
FROM table1;

INSERT INTO Examples

INSERT INTO users (user_name, location)
SELECT customer_name, location FROM customers;

The Difference between SELECT INTO and INSERT INTO
Which one to use between SELECT INTO and INSERT INTO? SELECT INTO creates a new table while INSERT INTO does NOT. For INSERT INTO, the table must exist else you need to create it.

You can read more about SELECT INTO – copying data into new table and INSERT INTO – copying data into existing table.