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.
